mysql - subqueries and joins

Question

I'm not quite sure if this is the right approach, this is my situation: I'm currently trying to select 15 galleries and then left join it with the user table through the id but I also want to select one random picture from each gallery however from what I know you can't limit the left join (picture) to only pick up one random picture without doing a subquery. Here is what I got so far but its not working as it should: ``` SELECT galleries.id, galleries.name, users.username, pictures.url FROM galleries LEFT JOIN users ON users.id = galleries.user_id LEFT JOIN pictures ON ( SELECT pictures.url FROM pictures WHERE pictures.gallery_id = galleries.id ORDER BY RAND() LIMIT 1) WHERE active = 1 ORDER BY RAND() LIMIT 15 ``` I also tried to do this with Active Record but I got stuck after doing two left joins, is it possible to do get a subquery in here: ``` $this->db->select('galleries.id, galleries.name, users.id as user_id, users.username'); $this->db->from('galleries'); $this->db->join('use…

Accepted Answer

You could fetch a random picture in a subquery:

```
select 
    g.name, u.username,
    (select url from pictures p where p.gallery_id = g.gallery_id 
     order by rand() limit 1) as url
from galleries g
left join users u on g.user_id = u.id
where g.active = 1
```

Based on your comment, you could select a picture for each gallery in a subquery. This is assuming the picture table has an ID column.

```
select 
    g.name, u.username, p.url, p.name
from (
    select id, user_id, name,
        (select id from pictures p 
         where p.gallery_id = g.gallery_id 
         order by rand() limit 1) as samplepictureid
    from galleries
    where g.active = 1
) g
left join users u on g.user_id = u.id
left join pictures p on p.id = g.samplepictureid
```

mysql - subqueries and joins

Solution

Problem