Fetch data from one table and compare with another one basic on contain values

I got 2 different databases and 2 tables

Here’s Table.1:

Table.1
username | date | phone number | rank                   |
user1    | 2021 | xxx xxx xxxx | ALL                    |
user2    | 2021 | xxx xxx xxxx | river, domain, CW, road|
user3    | 2021 | xxx xxx xxxx | river, CW              |
user4    | 2021 | xxx xxx xxxx | owl, gold, moon, DD    |

and there’s Table.2

Table 2
rank    | CODE | locations | contain | price  | exp |
river   | WT-2 | xxx xxx xx| JRCOW20 | 500.00 | --- |
road    | CC2W | xxx xxx xx| ------- | 200.00 | --- |
owl     | 568T | xxx xxx xx| JCCW120 | 300.00 | --- |
owl     | CCCD | xxx xxx xx| CWFGTFF | 100.00 | --- |
CW      | PTR1 | xxx xxx xx| 09WWKAL | 100.00 | --- |
CW      | 1RRW | xxx xxx xx| WFR4444 | 300.00 | --- |

but the users ask for all items separately according

searching the ranking.values using Table.2 and compare with Table.1

How can I use Sql command having the result when something like :

rank    | CODE | locations | contain | price  | exp |
river   | WT-2 | xxx xxx xx| JRCOW20 | 500.00 | --- |
road    | CC2W | xxx xxx xx| ------- | 200.00 | --- |
CW      | PTR1 | xxx xxx xx| 09WWKAL | 100.00 | --- |
CW      | 1RRW | xxx xxx xx| WFR4444 | 300.00 | --- |

but if when the user login as user4 than I would get

rank    | CODE | locations | contain | price  | exp |
owl     | 568T | xxx xxx xx| JCCW120 | 300.00 | --- |
owl     | CCCD | xxx xxx xx| CWFGTFF | 100.00 | --- |

I try to figure out the SQL parts and the following works has been tested; but no luck:

SELECT
    *
FROM
    [DB-1].[Table.1]
JOIN 
    [DB-2].[Table.2]
WHERE
    [DB-2].[Table.2] 
IN
    (
    [DB-1].[Table.1].[Col-Rank]
    )

but so far some of them come out either empty, of just output single result as:

login as *user2* result:
user2    | 2021 | xxx xxx xxxx | river, domain, CW, road|
river    | WT-2 | xxx xxx xxxx | JRCOW20 | 500.00 | --- |

login as *user4* result:
user4    | 2021 | xxx xxx xxxx | owl, gold, moon, DD    |
owl      | 568T | xxx xxx xxxx | JCCW120 | 300.00 | --- |

I don’t understand where should I change the detail and honestly this is very new problem to me

currently using MySQl 7.4, by PhpMyAdmin also PHP 7.4

There could be a lot informal statement come out of PhpMyAdmin; still trying to make everything work; any help would be appreciate!

Below are the questions I have tried:

sql-query-multiple-records-against-one-column-value-need-to-compare-another

mysql-check-if-a-column-has-values-based-on-another-column

mysql-how-to-check-for-a-value-in-all-columns

Answer

I think this just works fine, according of the WHERE … IN ();

also the results of rank comes from another application, honestly I haven’t had the clue to ask for it with another team

signal as Table.2 I used same method as MySQL query finding values in a comma separated string

SELECT
    *
FROM
    `signal`
WHERE
    `rank` IN(
        'river',
        'domain',
        'CW',
        'road'
    );

but I work it in the PHP file, so that’s why it may look won’t work as expect

PHP file
$sig = str_replace(',' , '','', $_SESSION['sig']);

$sig = str_replace(',' , '","', $_SESSION['sig']);

Advertisement

Answer