How can I select only first picture from a table?

Question

How can I select ALL data from the first table and only select FIRST ROW icon from the other table if their ID's are equal?

My query ((SELECT icon from pages p2 ...) is not working):

SELECT * 
FROM project p
INNER JOIN grouping g ON p.ID = g.ID
           (SELECT icon FROM pages p2 WHERE p2.ID=p.ID
            ORDER BY picture LIMIT 0, 1)
WHERE g.num ='" . $id . "'
ORDER BY g.sort ASC

icon in the query is the thumbnail I need, but the sorting itself is based on picture column.

Answer 1

You can use a correlated subquery:

SELECT p.*,
       (SELECT pp.icon
        FROM pages pp
        WHERE pp.ID = p.ID
        ORDER BY pp.picture
        LIMIT 0, 1
       ) as icon
FROM project p     
WHERE g.num = ?
ORDER BY g.sort ASC;

Note that you should be passing in the g.num as a parameter, not munging the query string. So, I changed the logic to use a parameter placeholder.