Query get parts not exist on tradecode table return null result why ?

Question

0.00/5 (No votes)

See more:

problem

the statement below return values without any problem

SQL

select top 1000000  p.PartNumber,m.Supplier,m.TradeCodeType,m.status from #PartsMissed m 
inner join parts.Nop_Part p   on p.CompanyID=m.SupplierId 
where
(p.PartID not in (SELECT PartID FROM parts.TradeCodes t where t.CodeTypeID=m.TradeCode))

I try to do above statement with left join direct but it not return any
values
it return null why

SQL

create table #PartsMissed
(
Supplier    nvarchar(200),
SupplierId  int,
TradeCodeType  nvarchar(100),
TradeCode  int,
status      NVARCHAR(100) NULL
)


insert into #PartsMissed
( 
	SupplierId,
	TradeCode
 )  
    VALUES

	(1000234,849774)

What I have tried:

statement below return null result why
although the first statement using ( not in ) work perfect

SQL

SELECT  p.PartID, p.CompanyID
FROM   parts.Nop_Part      p 
LEFT OUTER JOIN (
     SELECT mp.PartID, mp.CompanyID,d.TradeCode
     FROM parts.Nop_Part mp  left join #PartsMissed d on mp.CompanyID=d.SupplierId 
     left JOIN parts.TradeCodes AS t  ON t.CodeTypeID = d.TradeCode
) x ON p.PartID=x.PartID
WHERE x.PartID is  NULL

Posted 26-Jan-20 4:56am

ahmed_sa

Updated 28-Jan-20 9:03am

Wendelius

v3

Add a Solution

2 solutions

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)

OriginalGriff · Answer 1 · 2020-01-26T05:19:00

Solution 1

Well ... it can't return anything else ...

SQL

SELECT  p.PartID, ...

SQL

LEFT OUTER JOIN (
   ...
) x ON p.PartID=x.PartID

SQL

WHERE x.PartID is  NULL

Since x.PartID must be NULL, and x.PartID must be equal to p.PartID, the only values it can return are NULL.

Posted 26-Jan-20 5:19am

OriginalGriff

Comments

Richard Deeming 28-Jan-20 14:56pm

That's not how a LEFT JOIN works. :)

Richard Deeming · Answer 2 · 2020-01-28T09:05:00

Simplify your second query, and the problem should be obvious:

SQL

SELECT
    ...
FROM
    parts.Nop_Part As p
    LEFT JOIN
    (
        SELECT mp.PartID
        FROM parts.Nop_Part As mp
    ) As x
    ON x.PartID = p.PartID
WHERE
    x.PartID Is Null

Or, putting that a different way:

SQL

SELECT
    ...
FROM
    parts.Nop_Part As p
WHERE
    Not Exists
    (
        SELECT 1
        FROM parts.Nop_Part As mp
        WHERE mp.PartID = p.PartID
    )

How many parts are there going to be in your table which don't exist in the same table? :)