Help with Query 1

[ermora]

Alright, so I have this many-to-many relationship that has the following structure:

Part_Log <---> PN_Cnn <---> Part_Info
PartID PartID InfoID
Description InfoID Manufacture

What I need to retrieve is a list of all PartIDs found in the Part_Log that have not already been associated to a specific InfoID in the Part_Info table.

Sounds easy, but...

Thanks,

 

[SQLBill]

Maybe:

[code}
SELECT PL.PartID
FROM Part_Log PL
WHERE PL.PartID NOT IN (SELECT PartID
FROM PN_Cnn)
[/code]
or

SELECT PL.PartID
FROM Part_Log PL
 LEFT OUTER JOIN PN_Cnn PC
   ON PL.PartID = PC.PartID
WHERE PC.InfoID IS NULL

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Does this do it for you?

Select Part_Log.PartId
From   Part_Log
       Left Join PN_Cnn 
          On Part_Log.PartId = PN_Cnn.PartId
Where  PN_Cnn.InfoId Is NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Bill, sorry. Had I seen your post, I wouldn't have bothered, especially since your 2nd query is the same as mine (except you use table aliases).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Like thinking minds. But I won this time.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

can there be data in the pn_cnn table but missing in the part_info table?

If yes then something like this

SELECT PL.PartID
FROM Part_Log PL
 LEFT OUTER JOIN PN_Cnn pc
   ON pl.PartID = pc.PartID
LEFT OUTER JOIN Part_Info pn
   ON pc.InfoID = pn.InfoID
WHERE pn.InfoID IS NULL

or this

SELECT PL.PartID
FROM Part_Log PL
 JOIN PN_Cnn pc
   ON pl.PartID = pc.PartID
LEFT OUTER JOIN Part_Info pn
   ON pc.InfoID = pn.InfoID
WHERE pn.InfoID IS NULL

since you didn't post DDL and sample data I have to guess and this is the answer

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ermora]

SQLBill,
Thanks for your input and rapid answer - it almost seemed too easy for you

The first example you mentioned:
SELECT PL.PartID
FROM Part_Log PL
WHERE PL.PartID NOT IN (SELECT PartID
FROM PN_Cnn)
does indeed work. A minor modification and it worked like a charm.

Again, Thank You...

gmmastros,
I couldn't get the results I was looking for but seriously appreciate your input as it helped me to see the different ways on doing the join in my need.

Thanks!