How to display parts not exist on trade code temp and exist on parts temp table by trade code ?

Question

1.00/5 (1 vote)

See more:

I need to select parts that exist on temp table #parts and not exist on  tradecode temp table 

but by trade code not by partid only

suppose i have trade code related to it 2 parts on trade code temp table

then i will display all parts not related to tradecode temp table by trade code

on my case
1- I have trade code 15 on #tempsupplier
2- I will search on temp table TradeCodes for tradecode 15
3-I Found parts related to 15 on Tradecodes temp table 
4-I will get missing parts not related to 15 from  temp table parts
5- SO I WILL SHOW 100,200

What I have tried:

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

Posted 21-Jan-20 15:07pm

ahmed_sa

Updated 22-Jan-20 1:56am

v4

Add a Solution

Comments

Ankur Ramanuj 22-Jan-20 2:37am

What output you want if tradecode is null? and what do you want when tradecode having values?
Can you elaborate it easy what blocker you are facing?

ahmed_sa 22-Jan-20 6:14am

i explain more and post final result at end

2 solutions

Solution 1

Its a simply query that do not require master level skills. All you need to know about different type of Joins. We advice you to read good articles about JOINs.

Try this

SQL

select P.PARTID, P.SUPPLIERID
from #PARTS P
left outer join #TradeCodes T on P.PARTID = T.PARTID
WHERE T.PARTID IS NULL

Posted 22-Jan-20 0:24am

_Asif_

Comments

ahmed_sa 22-Jan-20 7:33am

I need missing parts on level of tradecode not level of part meaning parts have related parts 1,2,3 then i will search for missing on temp table parts meaning any part different from 1,2,3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2020-01-22T01:57:00

There are 2 easy ways to do what you want.

The first is to use a Subquery; which in your case gets all of the PartIDs from #Tradecode and removes it from the main query (#Parts) results
Subqueries (SQL Server) - SQL Server | Microsoft Docs[^]

SQL

SELECT PartID, SupplierId 
FROM   #Parts 
WHERE  PartID NOT IN (SELECT PartID FROM #TradeCodes)

The other method is to use an OUTER JOIN between those two tables, and choose to show only those records from #Parts that do not have a match in #Tradecodes.
Outer Joins - SQL Server | Microsoft Docs[^]

SQL

SELECT p.PartID, p.SupplierId 
FROM   #Parts               p
LEFT OUTER JOIN #TradeCodes t ON p.PartID = t.PartID
WHERE   t.PartID IS NULL

I would recommend that you read through both of the referenced articles to that you understand how these items work.
When you have 2 or more possible queries which will do the same thing, I would recommend using your IDEs (eg SSMS) tools to see which query is more efficient ("Show Actual Execution Plan"); and obviously choose the more efficient one.
For this case, they are near equal in performance.