0

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

PaPaFox552
  • 82
  • 7
  • The design of `Table.1` should store each user rank in it's own row instead of `ALL` or comma-separated values. The current design makes it harder to construct the query and possibly will impact the performance. If you can re-design the table, then, you should do it. Anyway, what MySQL version are you using? – FanoFN Oct 21 '21 at 02:26
  • MySQL version is 7.4, also PHP 7.4 and I think you guys are right, we should change the design – PaPaFox552 Oct 21 '21 at 03:06
  • I think you're mistaken with the MySQL version because there's no v7.4. You can check the version by running this query `SELECT version();` You should get a result [like this](https://dbfiddle.uk/?rdbms=mysql_8.0). – FanoFN Oct 21 '21 at 03:07
  • Got the version(): 8.0.26-0ubuntu0.20.04.3, Yup that make me understanding as the version of system, since link with ubuntu – PaPaFox552 Oct 21 '21 at 04:02
  • 1
    Great! I see you've decided to change the table design. Once you've done that, construct a workable query then you can post an answer (and probably accept it too) on your own question. I'm looking forward to what you come up with :) – FanoFN Oct 21 '21 at 04:10

2 Answers2

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

cottton
  • 1,522
  • 14
  • 29
  • Thanks a lot, I get the point of suggest change the structure of *RANK* I'm gonna test those solution right now – PaPaFox552 Oct 21 '21 at 03:10
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']);
PaPaFox552
  • 82
  • 7
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 25 '21 at 01:59
  • `WHERE IN()` definitely does not work as you intend when you have delimited strings in a column. This will not work for the sample data that you have offered. https://stackoverflow.com/q/3653462/2943403 – mickmackusa Oct 25 '21 at 03:26
  • `IN()` might work on Table 2, but it won't work on Table 1. Maybe `FIND_IN_SET()` https://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string `signal` isn't mentioned anywhere in your question. – mickmackusa Oct 25 '21 at 03:31