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.

FinalResults
Southern_GentlemanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
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....
AndyAinscowFreelance programmer / ConsultantCommented:
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 WillsTopic AdvisorCommented:
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 :)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Southern_GentlemanAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
I was returning the products from #temp2 that werent in #temp1, and didnt think to incorporate the ones that already were.

I wasnt concerned about the grouping, it was more "Does #temp2.productcode exist anywhere in #temp1"

Mainly because there is no productname in #temp1

But I will go back and double check the numbers, the code, the approach....

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Southern_GentlemanAuthor Commented:
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 WillsTopic AdvisorCommented:
Good work :)  Would have chosen your own Answer.... So, I had to give it the thumbs up !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.