CODEWITHSUNDEEP
mysqlleft-join
XTRUST.ORG
XTRUST.ORG

Reputation: 3392

mysql | Conditional left join request

I have a tables:

votes

record_id   user_id   type

1           1         0
2           1         1
3           1         0

question // type - 0

id   title
1    Title1
2    Title2 
3    Title3

answer // type-1 

id   question_id

2    2

I need request to get this output data

record_id   user_id   type   title

1           1         0      Title1
2           1         1      Title2
3           1         0      Title3

I think this will be conditional left join (if type=0 left join with questions table; if type=1 left join with answer table). How can I do this?

Upvotes: 0

Views: 59

Answers (2)

fthiella
fthiella

Reputation: 49049

If I understand your logic correctly, this should give the result you're looking for:

SELECT
  v.record_id,
  v.user_id,
  v.type,
  COALESCE(q1.title, q2.title) as title
FROM
  votes v LEFT JOIN question q1
  ON v.type=0 AND v.record_id=q1.id
  LEFT JOIN answer a
  ON v.type=1 AND v.record_id=a.id
  LEFT JOIN question q2
  ON a.question_id=q2.question_id

Upvotes: 1

asbb
asbb

Reputation: 21

SELECT
  votes.record_id, votes.user_id, votes.type, question.title
FROM
  votes
  LEFT JOIN question ON votes.type=0 AND question.id=votes.record_id
  LEFT JOIN answer ON votes.type=1 AND answer.id=votes.record_id

Upvotes: 0

Related Questions