CODEWITHSUNDEEP
sqlsql-server
user3822175
user3822175

Reputation: 33

Select from two tables compare on id remove if certain column has data

I have two tables. I'll stick the outlet in for reference.

Outlet

ID  Outlet_Name
-----------------
1   Big Bar
2   Bigger Bar

Master_Stock

ID  Stock_Name
--------------------
1   Heineken
2   Speights ALe
3   Lion Red
4   Chavey Blue
5   Tui Beer

Outlet_Stock

ID  Stock_ID  Outlet_ID
--------------------------
1   1         1
2   2         1
3   3         1

How can I select all that is in Master_Stock, compare it against Outlet_Stock and remove it based on the Outlet_ID ?

For example - the query would leave Master_Stock 

ID 
4
5

as Outlet 1 already has 1, 2 and 3.

This is running inside a Microsoft SQL Server Management.

SELECT *
FROM Stock
WHERE NOT EXISTS (SELECT *
                  FROM outlet_stock
                  WHERE stock.id = outlet_stock.stock_id 
                    AND outlet_stock.outlet_id <> '1')

I expect the output to only contain the ones that are in master stock that aren't in outlet_stock for a certain outlet

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

A big part of the solution is selecting from master_stock rather than stock. Then you need to get the subquery right:

SELECT ms.*
FROM master_stock ms
WHERE NOT EXISTS (SELECT 1
                  FROM outlet_stock os
                  WHERE ms.id = os.stock_id AND
                        os.outlet_id = 1
                 );

Upvotes: 1

Related Questions