asked on

Trouble with Not Exists

I'm using the following tables, my objective is for each baseid and iata in #temp1 to be visible and I need to retrieve all the productcode and productnames in #temp2 that don't exist in #temp1. I'm looking up and comparing the productcode in #temp2. I'm just having a hard time using the NOT EXIST so in my final select results i want to have #temp1.baseid, #Temp1.iata, #Temp2.productcode,#Temp2.productName to be visible. If there is another way to do this, i'm all ears.

IF OBJECT_ID('TempDB..#temp1') IS NOT NULL DROP TABLE #Temp1
IF OBJECT_ID('TempDB..#temp2') IS NOT NULL DROP TABLE #Temp2

CREATE TABLE #temp1
(baseid VARCHAR(10),
Iata VARCHAR(10),
amount DECIMAL(18,4),
programid INT,
productcode VARCHAR(50),
ProgramDescription VARCHAR(255))

INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('I77','VNY',NULL,1054,'131020','State Fuel Tax - Austin')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('I77','VNY',NULL,1053,'131065','State Fuel Tax - AEG')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('I77','VNY',NULL,1011,'131095','State Fuel Tax - Sentient')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('I77','VNY',NULL,1000,'131072','State Fuel Tax - UAS')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('I77','VNY',NULL,1000,'131073','State Fuel Tax - Jetex')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('I77','VNY',NULL,1000,'131085','State Fuel Tax - Shell')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('I77','VNY',NULL,1051,'131050','State Fuel Tax - UVAir Contract')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('I77','VNY',NULL,1056,'131040','State Fuel Tax - World Contract')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('L21','SAN',0.02,1000,'131072','State Fuel Tax - UAS')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('L21','SAN',0.02,1000,'131073','State Fuel Tax - Jetex')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('L21','SAN',0.02,1000,'131085','State Fuel Tax - Shell')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('L21','SAN',0.02,1011,'131095','State Fuel Tax - Sentient Jet')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('L21','SAN',0.02,1051,'131050','State Fuel Tax - UVAir Contract')
INSERT INTO #Temp1 (baseid,Iata,amount,programid,productcode,ProgramDescription) Values ('L21','SAN',0.02,1054,'131020','State Fuel Tax - Austin')

CREATE TABLE #temp2
(programid INT,
ProgramidDesc VARCHAR(255),
productcode VARCHAR(10),
productname VARCHAR(255)
)
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1000,'State Fuel Tax - Shell','131085','Shell Contract')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1000,'State Fuel Tax - UAS','131072','UAS Contract')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1000,'State Fuel Tax - Jetex','131073','EX Contract')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1011,'State Fuel Tax - Sentient Jet','131095','Everest / Sentient')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1050,'State Fuel Tax - Colt Contract','1310','Colt Contract')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1051,'State Fuel Tax - UVAir Contract','131050',' UvAir Contract')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1052,'State Fuel Tax - AvFuel Contract','131075','Av Contract')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1053,'State Fuel Tax - AEG','131065','AEG Contract ')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1054,'State Fuel Tax - Austin','131020','Austin Contract')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1056,'State Fuel Tax - World Contract','131040','World Contract')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1058,'State Fuel Tax - Pafco Contract','131080','Pafco Contract')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1060,'State Fuel Tax - Mercury Contract','131015','Contract Mercury')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1061,'State Fual Tax - Air BP Contract','131027','AIR BP CONTRACT')
INSERT INTO #Temp2 (programid,programidDesc,productcode,productname) VALUES (1062,'State Fuel Tax - EasternContract','131086','Eastern Contract')

Open in new window

This is the final results i would like query to output. as you can see all the productcodes and productnames are pulled from #temp2 since they are not originally in
#temp1. The baseid and iata can increase or decrease based on the non existing products.

Mark Wills

Well, for a start, thankyou for the scripts.

It does make a difference :)

The not exists isnt a problem, the challenge is, it it doesnt exist in table1 then how do we represent / attach it to a baseid or iata ?

Or dowe apply to each of baseid + iata

Back in a while....

AndyAinscow

You might want to look at the different types of JOIN you can use in a query.
https://www.w3schools.com/sql/sql_join.asp

With that you can connect two tables, pull all records and the use a restriction that the key at one side is null. I think that is what you are trying to do.

Mark Wills

Just to be different, thought I would use an except query

  ;with cte as
  (select t2.productcode,t2.productname
   from #temp2 t2
   except 
   select t2.productcode, t2.productname
   from #temp1 t1
   inner join #temp2 t2 on t1.productcode = t2.productcode
 ) select baseid,Iata,productcode,productname 
   from cte cross join (select distinct baseid,iata from #temp1) t1
   order by 1,2,3

Open in new window

And can do it other ways - let me know
in the meantime except read : https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-2017 it is kind of handy :)

Southern_Gentleman

ASKER

Thank you Topic Advisor, i think we are on the right track. There are couple of products that are missing from each baseid. If baseid L21 has 6 product codes in #Temp1 it should return 8 product codes since there are a total of 14 product codes in #Temp2. For baseid I77 it has 8 products in #temp1. it would return 6 product codes

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Southern_Gentleman

ASKER

worked on it for a while and continued with the not exists so it ended up correctly. Thank you for your efforts

[code]
SELECT c.baseid,c.Iata,b.productcode,b.productname AS productname
FROM #Temp2 b CROSS APPLY (SELECT DISTINCT baseid,Iata FROM #Temp1) AS c
WHERE NOT EXISTS (SELECT * FROM #Temp1 AS d WHERE d.baseid = c.baseid AND d.productcode = b.productCode)[/code]

Mark Wills

Good work :) Would have chosen your own Answer.... So, I had to give it the thumbs up !