How to write this statement by using join without using (not in) statement ?
I work on SQL server 2012
I need to use alternative query statement to (not in)
using join and where condition only
Are this possible or not ?
What I have tried:
SELECT PartID, SupplierId
FROM #parts
WHERE PartID NOT IN (
SELECT t.PartID
FROM #tempsupplier AS s
INNER JOIN #TradeCodes AS t ON t.TradeCode = s.TradeCode
);
Details if you need
create table #tempsupplier
(
SupplierId int,
TradeCode int
)
insert into #tempsupplier(SupplierId,TradeCode)
values
(10,15)
create table #parts
(
PartID int,
PartNumber nvarchar(200),
SupplierId int,
TradeCode int
)
insert into #parts
(PartID,PartNumber,SupplierId)
values
(100,'silicon',10),
(200,'motherboard',10),
(300,'iron',10),
(400,'plastic',10),
create table #TradeCodes
(
PartID int,
TradeCode int
)
insert into #TradeCodes
(PartID,TradeCode)
values
(300,15),
(400,15)
FinalResult
partid companyid
100 10
200 10