MySQL show data from Rows to columns 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:



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:



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

Thanks in advance.
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.