[SOLVED] fetch data from one table and compare with another one basic on contain values [solved]

Issue

  • This question currently solved by mysql+php

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 :

login as user2 so I would get

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 | --- |

or

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

Solution

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

or

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

Answered By – ReturnError552

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published.