0

I am stuck at getting a query with joining 2 tables. Below is the database structure. What I want to achieve is to list all the information from one table with join on the second table but it has 3 conditions. I have written the following SQL but it's not working.I want only 2 rows returning all rows data in each 3 columns. Please advice.

Tables are: enter image description here enter image description here

My SQL:

SELECT b.*,
       CASE WHEN pc.caster_type = '1' THEN pc.name END as  primary_caster ,
       CASE WHEN pc.caster_type = '2' THEN pc.name END as  secondary_caster ,
       CASE WHEN pc.caster_type = '3' THEN pc.name END as  caster  
FROM broadcasts b 
left join rel_braodcast_casters pc ON pc.fk_broadcast_id = b.broadcast_id ;

Getting this result:

enter image description here

I do not want multiple records for each broadcast. I want to replace NULL values with corresponding rows of data.

Thanks in advance.

  • Add GROUP BY broadcast_id, wrap fields into some aggregate function(MAX, for example). Alternatively - join 3 separate copies of rel_brodcast_casters table. – Akina Jul 2 at 10:25
  • I tried with Group BY. But then it eliminates records for secondary caster for broadcast id 1 and similarly for others. And 3 joins query is not working for me. Maybe I have written incorrectly – Sameer Jul 2 at 10:28
  • Build fiddle, I'll show... – Akina Jul 2 at 10:30
  • sqlfiddle.com/#!9/83f530/2/0 This is fiddle – Sameer Jul 2 at 10:35
  • 1
0

Finally I got the answer. Below is the fiddle.

sqlfiddle.com/#!9/83f530/6 or sqlfiddle.com/#!9/83f530/7

Thanks to Akina who helped me a lot. I will prefer to use single join SQL over multiple join fiddle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.