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.
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:
I do not want multiple records for each broadcast. I want to replace NULL values with corresponding rows of data.
Thanks in advance.