Page 1 of 1

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

Posted: Mon Feb 07, 2005 10:48 pm
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

Posted: Mon Feb 07, 2005 10:59 pm
by feyd
check the "useful posts" thread in my sig.

Posted: Mon Feb 07, 2005 11:17 pm
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.