CODEWITHSUNDEEP
mysqljoin
InTry
InTry

Reputation: 1169

How to join two ON conditions on the same field

I have one table_A 

|  id  | status1 | status2 | 
+------+---------+---------+
|   1  |     2   |    3    |
+------+---------+---------+
|   2  |     1   |    3    |
+------+---------+---------+

and table_B

|  id  |  name   |  
+------+---------+
|   1  |   yep   | 
+------+---------+
|   2  |   nope  |   
+------+---------+
|   3  |   maybe |   
+------+---------+

How can I get the output to look like this?

1 =nope, maybe;
2 =yep, maybe

I tried something like this:

SELECT * FROM table_A a 
LEFT JOIN table_B b 
ON a.status1= b.id AND a.status2= b.id"

Upvotes: 1

Views: 97

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You want to do two joins, one for each status field:

SELECT a.id, b1.name as name1, b2.name
FROM table_A  a LEFT JOIN
     table_B b1 
     ON a.status1 = b1.id LEFT JOIN
     table_B b2
     on a.status2= b2.id;

EDIT:

For amusement's sake, you can do this with one join and aggregation:

select a.id,
       max(case when a.status1 = b.id then b.name end) as name1,
       max(case when a.status2 = b.id then b.name end) as name2
from table_A a left join
     table_B b
     on b.id in (a.status1, a.status2)
group by a.id;

However, the two-join version is actually simpler.

Upvotes: 2

very9527
very9527

Reputation: 959

select a.id, b1.name, b2.name from tableB as b1
inner join TableA as a
on b1.id = a.status1
inner join TableB as b2
on b2.id = a.status2

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26333

If you want a column to be exactly yep, maybe and nope, maybe, you'll need the GROUP_CONCAT function.

You can get away with one join if you "un-pivot" your table_a, which is what the subquery in my answer does:

SELECT
  a.id,
  GROUP_CONCAT(b.name ORDER BY b.name DESC)
FROM (
  SELECT id, status1 AS stat_id FROM table_a
  UNION SELECT id, status2 FROM table_a
) a
INNER JOIN table_b b ON a.stat_id = b.id
GROUP BY a.id;

The ORDER BY in the GROUP_CONCAT makes sure yep comes before maybe and that nope comes before maybe in the comma-separated list.

Upvotes: 2

Francis Gonzales
Francis Gonzales

Reputation: 495

Also you can do it like this:

SELECT * FROM table_A  a 
LEFT JOIN table_B b ON a.status1= b.id 
LEFT JOIN table_B bTmp ON a.status2= bTmp.id

Upvotes: 2

Related Questions