Display joined table records in one parent record

[dlite922]

Hey guys, I think this is very easy to do and I'm just having a brain fart.

I have a movie database that links to an actors database. 1 move = many actors of course but I want to display the actor information along with the movie like so

series_id, name, actors
123 | John Foo, Jay Bar, James Baz

the two tables are linked by actor_id

Here's what I got:

 
mysql> SELECT si.series_id, (SELECT a.full_name FROM actor AS a WHERE a.actor_id = sa.actor_id) AS actors
    -> FROM seriesimage AS si LEFT JOIN seriesactor AS sa ON sa.series_id = si.series_id
    -> WHERE si.series_id = 121428;
+-----------+--------------------------+
| series_id | actors                   |
+-----------+--------------------------+
|    121428 | Michael Clarke Duncan    |
|    121428 | NULL                     |
|    121428 | The Rock Douglas Johnson |
+-----------+--------------------------+
 
 

In my mind this is pretty popular and simple to do but I'm not putting the right keywords into Google.

Thanks,



Dan

[mwasif]

Try the following

SELECT si.series_id, 
GROUP_CONCAT(SELECT a.full_name FROM actor AS a WHERE a.actor_id = sa.actor_id) AS actors 
FROM seriesimage AS si LEFT JOIN seriesactor AS sa ON sa.series_id = si.series_id 
WHERE si.series_id = 121428
GROUP BY si.series_id;

I suggest you not to mix subquery with JOIN. Use INNER JOIN to get the names of the actors.

[dlite922]

Sweet! I'll try that next time. I don't remember GROUP_CONCAT. I better look that up.

You guys never disappoint the byte php forum n00bs!



Dan