Question about a JOIN I'm working on..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Question about a JOIN I'm working on..

Author

Topic

shawn_aker
Starting Member

2 Posts

Posted - 2002-09-20 : 00:10:36

I'm trying to remove order numbers that have incorrect partnumbers, but the partnumbers are on the line item level. How can I make sure that if any orders are bad, that it removes the order.ID in the Query statement?

Example:

Orders:
ID,
370,
371,
372,

SoldItems:
PartNumber,OrderID
part1,370
part2,370
part3,370

GoodPart:
GoodPart,
part1,
part3,

when I use this statement:
SELECT DISTINCT Orders.ID
FROM Goodpart
INNER JOIN SoldItems ON GoodPart.GoodPart = SoldItems.PartNumber INNER JOIN Orders ON SoldItems.OrderID = Orders.ID

it will return the 370 even though Part2 isn't there. How can I make it NOT return the 370? Maybe some kind of t-sql or something? Anything to point me in the right direction.

Thanks in advance!

Shawn

Edited by - shawn_aker on 09/20/2002 00:13:56

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-09-20 : 04:57:33

a
SELECT * FROM Goodpart
INNER JOIN SoldItems ON GoodPart.GoodPart = SoldItems.PartNumber INNER JOIN Orders ON SoldItems.OrderID = Orders.ID

would return the following data set....

part1, part1, 370, 370
part3, part3, 370, 370,
(a) (b) (c) (d)

(a) is goodpart.goodpart
(b) is solditems.partnumber
(c) is solditems.orderid
(d) is orders.id

putting a DISTINCT on ORDERS.ID MUST return a value of 370...because it's the only value coming back to you...in the join.

I thnk you need to re-work the query and look at using some form of NOT EXISTS and a SUBQUERY....

re-think what you are trying to do....establish what is the parent in the relationship between the tables and then work downwords from there...

it's not 100% clear from your question as to what your problem is....(partly because you seem to have worked backwards in the join statements compared to your statement in line 1)

you also need to define 'bad'...does it mean....
missing value? missing record? not = 370?

shawn_aker
Starting Member

2 Posts

Posted - 2002-09-20 : 12:04:45

Sorry about that.

What I want it to do is NOT return the order.ID 370 if it's missing anyone of those partnumbers. Will you give me an example of a NOT EXIST statement?

The problem with using subqueries (which I think I've tried correctly) is that because it's on a line item level, not on the orderline it will always return the 370 if any are matching.

I reworked the query a little bit:
SELECT DISTINCT dbo.Orders.ID
FROM Orders
INNER JOIN SoldItems ON Orders.ID = SoldItems.OrderID
INNER JOIN GoodPart ON SoldItems.PartNumber = GoodPart.GoodPart

Edited by - shawn_aker on 09/20/2002 12:06:19

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-09-20 : 12:21:11

you seem to have posted (asked) this question twice.....

once via AskSQLTeam (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19969), once when you registered under your own name....

the answer given for the AskSQLTeam may solve your problem....(it will give you a good example of the NOT EXISTS structure anyway.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources