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

This question will be keep update

I got 2 different database
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 xx| JRCOW20 | 500.00 | --- |

login as *user4* result:
user4    | 2021 | xxx xxx xxxx | owl, gold, moon, DD    |
owl     | 568T | xxx xxx xx| 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 is the question I’ve 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

Contents hide

Answers:

Method 1

Method 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

IMO rank (i.e. ‘river, domain, CW, road’) should be an extra table.

Or you may can use REGEXP. But i think this would cost a lot resources. Im not an expert.

Example (UNTESTED):

SELECT
    `tbl2`.*
FROM `Table1` AS `tbl1`
JOIN `Table2` AS `tbl2` ON `tbl2`.`rank` REGEXP REPLACE(`tb1`.`rank`,', ','|')
WHERE `username` = :username
;

Plan is to “find” the user in the table 1 by name,

and then join table 2 on rank

using REGEXP to check if the row matches f.e. ‘river|domain|CW|road’

(‘river’ OR ‘domain’ OR ‘CW’ OR ‘road’).

The REPLACE is to get the , to an |.

But like i said – im not an expert =)

Method 2

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']);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating