CODEWITHSUNDEEP
mysqljoinmultiple-columns
Smith
Smith

Reputation: 5961

mysql - Join tables based on multiple columns in second

I have two tables Place and Post which i need to generate an output 

place
--------------
std_id  sch_id
1       1
2       2
3       1
4       3
5       4
6       2
7       5
8       3
9       5
10      4

and

Post
------------
sch1_id  sch2_id  sch3_id  sup_id
1        2        3        1
3        2        4        2

I need to generate the following output

sup_id  sch_id  std_id
1       1       1
1       1       3
1       2       2
1       2       6
1       3       4
1       3       8
2       3       4
2       3       8
2       2       2
2       2       6
2       4       5
2       4       10

I tried this

 SELECT pl.std_id,po.sup_id FROM `place` pl,`post` po WHERE pl.sch_id =
 po.sch1_id group by po.sup_id

and got this

| std_id | sup_id |
|--------|--------|
|      1 |      1 |
|      4 |      2 |

EDIT

SQLFiddle

Upvotes: 3

Views: 71

Answers (3)

sagi
sagi

Reputation: 40481

Simply use IN() :

SELECT pl.std_id,pl.sch_id,po.sup_id
FROM `place` pl
INNER JOIN `post` po
 ON(pl.sch_id IN(po.sch1_id,po.sch2_id,po.sch3_id))

No need to normalize your data, although it is very recommended!! You should rethink about your table structure.

Upvotes: 3

jpw
jpw

Reputation: 44881

It looks like you want to form the join matching any of the sch* values, If so then one way would be to use multiple conditions in the join:

select sup_id, sch_id, std_id
from post p
join place pl on pl.sch_id = p.sch1_id 
              or pl.sch_id = p.sch2_id 
              or pl.sch_id = p.sch3_id

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can try 'un-pivoting' table post using UNION ALL:

SELECT pl.std_id, po.sch_id, po.sup_id
FROM `place` pl
JOIN (
   SELECT sch1_id AS sch_id, sup_id
   FROM `post`

   UNION ALL

   SELECT sch2_id AS sch_id, sup_id
   FROM `post`

   UNION ALL

   SELECT sch3_id AS sch_id, sup_id
   FROM `post`

   UNION ALL

   SELECT sch4_id AS sch_id, sup_id
   FROM `post`
) AS po ON pl.sch_id = po.sch_id

Upvotes: 2

Related Questions