CODEWITHSUNDEEP
mysqldatabase-designcompareresultset
ee12csvt
ee12csvt

Reputation: 11

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

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?

Upvotes: 0

Views: 8809

Answers (4)

nik
nik

Reputation: 3678

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)

Upvotes: 0

Salil
Salil

Reputation: 47512

use distinct if one item has more than one photograph.

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

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171511

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

Upvotes: 4

chris
chris

Reputation: 10003

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

should be what you want

Upvotes: 0

Related Questions