Mysql – need to join Vertical row as per values in Horizontal rows

MySQL

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) 

Best Answer

If I get it right

SELECT t1.`userId`, j.`job` 
FROM `table2` j
JOIN `table1` t1 ON t1.`state` IN (j.`to`, j.`from`) 
GROUP BY t1.`userId`, j.`job`
HAVING count(t1.'state') = 2