CODEWITHSUNDEEP
sql
user7932709
user7932709

Reputation:

SQL - need to return products that are NOT in one table

My query needs to return the products that are not sourced. All products are listed in the inventory table, but only the ones that have a vendor associated with them are in the vendor_item table. I have tried the following statement just to get back "no data found" 

SELECT i.item_name "Products Not Sourced", vi.item_num "Item Number"
FROM inventory i RIGHT OUTER JOIN vendor_item vi ON i.item_num=vi.item_num
WHERE vi.item_num IS NULL

Logical model this is all based on:-

enter image description here]

Upvotes: 1

Views: 314

Answers (2)

Shashank Kumar
Shashank Kumar

Reputation: 51

Hope you should use the normal joins.

Left and Right Outer Joins exclude some of the content in the table.

Upvotes: -1

SqlZim
SqlZim

Reputation: 38073

Try a left join instead of a right join with that order of tables. 

select 
   i.item_name "Products Not Sourced"
 , vi.item_num "Item Number"
from mnathan3_SID_inventory i
  left join mnathan3_SID_vendor_item vi 
    on i.item_num = vi.item_num
where vi.item_num is null

Otherwise reverse the order of the tables:

select 
   i.item_name "Products Not Sourced"
 , vi.item_num "Item Number"
from mnathan3_SID_vendor_item vi
  right join mnathan3_SID_inventory i
    on i.item_num = vi.item_num
where vi.item_num is null

Upvotes: 3

Related Questions