Question 63976 in PhpMyAdmin Question posted in
The official documentation can be found here.

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

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 :

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 xxxx | JRCOW20 | 500.00 | --- |

or

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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

  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search