How to select just 1 child table item for each parent record?

Question

I have a table of lists and a table of list items. I want to formulate a query to select just one list item for each item in the lists table. Here's some simple data to illustrate my question:

 'lists' table
id   updated   share
--- ---------- -----
1   2013-07-11   1
2   2013-07-13   0
3   2013-07-15   1
4   2013-07-14   0
5   2013-07-14   1

'list_items' table
id l_id description sort likes
-- ---- ----------- ---- -----
1   1    hello       0     3
2   1    goodbye     0     0
3   1    thanks      0     4
4   2    ok          0     0
5   3    love        0     2
6   3    hate        1     1
7   4    celebrate   0     0
8   5    party       0     1
9   5    summer      1     5
10  5    winter      2     2

Now say I want to get the first item from each shared list (share = 1). By first I mean if the list items were sorted by 'sort'.

The expected result based on the above data would be:

lists.id id l_id description sort likes
-------- -- ---- ----------- ---- -----
   1     1   1    hello       0     3
   3     5   3    love        0     2
   5     8   5    party       0     1

Update: I struggled to get my head around the solutions provided by peterm and hims056 and while kayla's solution looked more like something I could follow it didn't return the correct results. Taking ideas from these solutions I had a crack at it myself and came up with

  SELECT * FROM (
    SELECT lists.id AS listid, lists.share, list_items.* 
    FROM list_items, lists
    WHERE lists.id = l_id
    AND lists.share = 1 
    ORDER BY sort) q
  GROUP BY q.listid

This seems to work but as peterm points out, the values for the columns in select clause that are not part of group by clause may be ambiguous.

I though someone would come up with a solution using LIMIT as that was the way I was thinking about doing it first. You can return the list ids which allow sharing simply by:

SELECT lists.id FROM lists WHERE share = 1

and for a given list id you can return the top list item by:

SELECT lists.id AS listid, lists.share, list_items.* 
FROM list_items, lists
WHERE lists.id = l_id 
AND lists.id = 1
ORDER BY sort
LIMIT 1

But is there a way to put these 2 statements together to return top list item for each list that allows sharing?

Answer 1

UPDATED To ensure getting first per group with order by sort try

SELECT q.l_id list_id, q.id, i.description, i.sort, i.likes 
  FROM 
(
  SELECT l_id, id, @n := IF(@g = l_id, @n + 1, 1) n, @g := l_id g
    FROM 
  (
    SELECT i.l_id, i.id
      FROM list_items i JOIN lists l
        ON i.l_id = l.id 
     WHERE l.share = 1
     ORDER BY l_id, sort, id
  ) b CROSS JOIN (SELECT @n := 0, @g := 0) a
  HAVING n = 1
) q JOIN list_items i 
   ON q.id = i.id

Sample output:

| LIST_ID | ID | DESCRIPTION | SORT | LIKES |
---------------------------------------------
|       1 |  1 |       hello |    0 |     3 |
|       3 |  5 |        love |    0 |     2 |
|       5 |  8 |       party |    0 |     1 |

Here is SQLFiddle demo

Answer 2

SELECT lists.id, list_items.id, l_id, description, sort, likes 
  FROM (SELECT * FROM lists WHERE share = 1) lists 
  LEFT JOIN (SELECT * FROM list_items GROUP BY l_id) list_items 
  ON  lists.id = l_id

Answer 3

Since you want to get minimum list_items.id sorted by list_items.sort you need to perform double nested query like this:

SELECT tbl.l_id list_id, tbl.minID, li.description, li.sort, li.likes 
FROM list_items li
JOIN
(
    SELECT l.l_id,MIN(l.id) minID FROM list_items l
    JOIN
    (
        SELECT li.l_id,MIN(li.sort) sort FROM list_items li
          JOIN lists l ON li.l_id = l.id WHERE l.share = 1
         GROUP BY li.l_id
    ) l2
    ON l.l_id = l2.l_id
    AND l.sort = l2.sort
    GROUP BY l.l_id
) tbl
ON  li.id = tbl.minID;

• See this SQLFiddle
• See this SQLFiddle with different values.