need starting point on an ORDER BY

Question

The website contains images. These images can be ranked. When an image is ranked, the value can be 1,2, or 3. To save ranking I have a table ranking_items. The images are displayed as thumbnails. The boss would like me to order them by rank. the problem is, how do I also include images in the result with no entry in the ranking_items?

$db->query("SELECT file_name 
    FROM images, ranking_items 
      WHERE images.id=ranking_items.image_id 
       ORDER BY ranking_items.rank ASC");

score 0 · Accepted Answer · answered Mar 21 '12 at 19:10

0

When you do FROM table1, table2 you are doing a JOIN.

Try to LEFT JOIN the ranking_items table. This will return all rows, and put NULLS in the places where the join fails.

SELECT file_name 
FROM images
LEFT JOIN ranking_items ON images.id=ranking_items.image_id 
ORDER BY ranking_items.rank ASC

answered Mar 21 '12 at 19:10

gen_Eric

223,194
41
299
337

this worked, thank you. is it safe to say that right join would not include the nulls? – Johnny Craig Mar 21 '12 at 19:23
1

`RIGHT JOIN` is the opposite of `LEFT JOIN`. Meaning the NULLs would be where rows do not exist in the `images` table, compared to `LEFT JOIN` which adds NULLs when the row doesn't exist in the `ranking_items` table. For more info see: http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-full-join-in-mys – gen_Eric Mar 21 '12 at 19:26

score 0 · Answer 2 · answered Mar 21 '12 at 19:11

0

SELECT file_name 
FROM images LEFT JOIN ranking_items 
ON images.id=ranking_items.image_id 
ORDER BY ranking_items.rank ASC

answered Mar 21 '12 at 19:11

rfusca

7,435
2
30
34

need starting point on an ORDER BY

2 Answers2