sql join query question (mysql)

Question

I posted a question earlier- got it fixed but not i have another problem- i have a table with 4 records in it and my sql is only returning three records

I tried two types of join this here one

SELECT items.id, items.link, items.title, items.image, lists.user, lists.dated 
  FROM lists, items 
 WHERE lists.user = '506161637' 
   AND lists.item = items.id 
ORDER BY lists.dated;

...and the other:

SELECT * 
FROM items
JOIN lists ON items.id = lists.item
WHERE lists.user = '506161637'
LIMIT 0 , 30

the lists table is listed below and it returns the records with 8,6,5

id   user        item  dated
---------------------------------
20   506161637   1     2009-11-19
19   506161637   8     2009-11-19
18   506161637   6     2009-11-19
17   506161637   5     2009-11-18

Answer 1

I believe that just three records join with records from 'items' table.

Answer 2

The obvious answer is that your join is failing the join criteria for row 1. Likely your Items table does not have an entry where it's id is in lists.item.

Answer 3

Without seeing the data I would assume one of the 'item' numbers does not exist in the item table, so it is not able to join on that. In that case you could use a left join (or right join depending on which you select from first) if you still want the 'list' element returned.

example:

   SELECT * 
     FROM items
LEFT JOIN lists ON items.id = lists.item
    WHERE lists.user = '506161637'
   LIMIT 0 , 30

See this page on left joins

Answer 4

Sorry guys, it wasn't anything to do with the SQL at all. I cut code into a function and didn't change the variable names and the code became an array so only the first number was actually going into the db.

Thanks