Rajendra Khabiya Rajendra Khabiya - 7 months ago 9
SQL Question

Need to join two tables as per result of Group_concat

TABLE 1



+----+--------+-----+
| id | userId |state|
+----+--------+-----+
| 1 | 1 |AZ |
| 2 | 1 |AK |
| 4 | 1 |AL |
| 5 | 1 |CO |
| 6 | 1 |CA |
| 7 | 2 |AZ |
| 8 | 2 |AK |
| 9 | 2 |AL |
+----+--------+-----+


TABLE 2



+----+---------+--------+
| id | job | from | to |
+----+------+------+----+
| 1 | job1 | AZ |AK |
| 2 | job2 | AL |CO |
+----+---------+--------+


I want list of Job as per their to & from both state allowed by user in mysql, For above it will return

+------+--------+
| userId | job |
+--------+------+
| 1 | job1 | // As User 1 will have state AZ & AK
| 1 | job2 | // As User 1 will have state AL & CO
| 2 | job1 | // As User 2 will have state AZ & AK
+----+----------+


used query:



SELECT hs.userId, j.job FROM `table2` j

JOIN (
SELECT userId,GROUP_CONCAT(CONCAT('\'', `state`, '\'' )) as stateList FROM `table1` GROUP BY userId
) hs ON j.`to` IN (stateList) AND j.`from` IN (stateList)

Answer

Joining twice should be faster than using a group_concat:

SELECT u1.userId, j.job
FROM `table2` j
JOIN `table1` u1 ON u1.state = j.from
JOIN `table1` u2 ON u2.state = j.to AND u2.userId = u1.userId

It's been a little while since I used mysql, but if the IN clause works similar to MS SQL Server then it does not work with a comma-separated string argument, the comma separation must be outside of the string. You would need to use a string comparison instead (the LIKE operator for example).

SELECT hs.userId, j.job FROM `table2` j
JOIN (
    SELECT userId,GROUP_CONCAT(CONCAT('\'', `state`, '\'' )) as stateList FROM `table1` GROUP BY userId   
) hs ON stateList LIKE CONCAT('%', j.`to`, '%') AND stateList LIKE CONCAT('%', j.`from`, '%')
Comments