I have three tables:

producer: Producer

series_producer: series_producer

and series: series

What i am trying to do is to do a select on producers and also show the names of the series they have created. ex.

producer_id    producer_name    producer_series
1              Marta Kauffman   Friends, Dream On

I am able to show it with the id of the series, but i want the name instead..

This is my SQL to do that:

-- View all series from one producer
select producer_id, producer_name, group_concat(series_producer_sid) as prods
from producer w join
     series_producer uw
     on producer_id = uw.series_producer_pid
group by producer_id;

How can i do it?

Thanks in advance!

CodePudding user response：

Try this:

select 
  producer_id, 
  producer_name, 
  group_concat(s.series_name) as prods
from producer as w 
join series_producer as uw
  on w.producer_id = uw.series_producer_pid
join series as s
  on uw.series_producer_sid = s.series_id
group by producer_id;