Two linked tables, pull back any records that dont match?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Two linked tables, pull back any records that dont match?

Post by Stryks »

Hi,

I have two tables, one a master table of items, and a second which references the first. I am trying to get a list of all items in table 1 which have not be referenced in table 2 but I cant seem to get it to work because of the fact that the column which is different is also the one which links the tables together.

Code: Select all

SELECT 
  `tbl_master_items`.`name`
FROM
  `tbl_vendor_menu_items`
JOIN `tbl_master_items` ON (`tbl_vendor_menu_items`.`MI_ID` <> `tbl_master_items`.`MI_PK`)
WHERE
  (`tbl_master_items`.`MT_ID` = '11')
I was hoping that something like the above would have worked, but it returns duplicate results including ones that have been used.

I hope that makes sense. Anyhow ... can anyone give me an idea of how/ if this can be accomplished?

Cheers
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

check the "useful posts" thread in my sig.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Cheers :D

Once again feyd, you're a life saver.

Code: Select all

SELECT 
  `tbl_master_items`.`name`
FROM
  `tbl_master_items`
  LEFT JOIN `tbl_vendor_menu_items` ON (`tbl_master_items`.`MI_PK` = `tbl_vendor_menu_items`.`MI_ID`)
WHERE
  (`tbl_vendor_menu_items`.`MI_ID` IS NULL) AND 
  (`tbl_master_items`.`MT_ID` = '11')
Seems to work a charm.
Post Reply