sql join query question (mysql)

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
-开发者_如何学Python--------------------------------
20   506161637   1     2009-11-19
19   506161637   8     2009-11-19
18   506161637   6     2009-11-19
17   506161637   5     2009-11-18

---

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

---

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.

---

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

---

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