Need a MYSQL query to compare two tables and only output non matching results

Question

I have two tables in my database, one contains a list of items with other information on these items. The other table is contains a list of photographs of these items.

The items table gives each item a unique identifier,which is used in the photographs table to identifier which item has been photographed.

I need to output a list of items that are not linked to a photograph in the second table. Any ideas on how I can do this?

Answer 1

SELECT * FROM items WHERE id NOT IN (SELECT item_id FROM photos);

should be what you want

Answer 2

select i.*
from Items i
left outer join Photographs p on i.ID = p.ItemID
where p.ItemID is null

Answer 3

use distinct if one item has more than one photograph.

SELECT * FROM items WHERE id NOT IN (SELECT distinct(item_id) FROM photos);

Answer 4

SELECT id,name from tbl_item 
WHERE id NOT IN (SELECT distinct(tbl_item.id) FROM tbl_item INNER JOIN tbl_photo ON tbl_photo.pid=tbl_item.id)