Problem using NOT EXISTS with multiple rows

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 2008 Forums

Transact-SQL (2008)

Problem using NOT EXISTS with multiple rows

Author

Topic

daylward
Starting Member

5 Posts

Posted - 2011-06-28 : 02:44:24

Hi, this is my first post, so forgive me if the answer to my question can be easily found elsewhere - I did try! Hopefully someone familiar with using NOT EXISTS can answer this fairly quickly.

I have a somewhat complex query, but I've distilled it down to this. Let's start with some data, in four tables (WKO, WOO, WOM and WCL). I've limited it to one WorkOrderID, and deleted all the irrelevant columns:


WKO_WorkOrderID	    WKO_ItemID	
50213	            H3-1903-100	

WOO_WorkOrderID		WOO_OperationSeqID	WOO_WorkCenterID
50213				10			10
50213				20			30
50213				30			20
50213				35			90

WOM_WorkOrderID		WOM_OperationSeqID	
50213				10	
50213				20	
50213				30	

WCL_WorkCenterID	WCL_WorkOrderID		WCL_OperationSeqID
10				50213			10
30				50213			20
20				50213			30
90				50213			35

Now, here's my query:


      select distinct WCL.WCL_WorkOrderID, WCL.WCL_OperationSeqID, WCL.WCL_WorkCenterID
      from WCL
	  where not exists 
		(select * from WOM 
		 where 
		 WOM.WOM_WorkOrderID = WCL.WCL_WorkOrderID and 
		 WCL.WCL_WorkCenterID between 20 and 40)
      and
      wcl.WCL_WorkOrderID = '50213'

Here are the results it gives:


WCL_WorkOrderID		WCL_OperationSeqID	WCL_WorkCenterID
50213				10			10
50213				35			90

So, this may seem perfectly logical to someone who is well versed. But it's not what I want. I'd like the query to return nothing. To be more specific, if a record exists in the WOM table for a WorkOrderID that has a WorkCenterID between 20 and 40, I would expect the "NOT EXISTS" subquery to return a result, and therefore the main query should return no results for that WorkOrderID. Apparently it is returning no results for WorkCenterID's of 10 and 90 (because they're not between 20 and 40), which is why I'm getting those records back, but how do I change it so that if records exist for WorkCenterID's between 20 and 40 for that WorkOrderID, no records are returned by the main query for that WorkOrderID? In other words, how do I write it so that the ONLY records returned are those that have no records in the WOM table with WorkCenterID's between 20 and 40 (even if they do have records in the WOM table outside that range)?

I hope that's clear enough for someone to answer easily and quickly... :-) Thank you so much in advance!

Dan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-28 : 03:42:42

Please post the EXPECTED result, not the erraneous result.

N 56°04'39.26"
E 12°55'05.63"

daylward
Starting Member

5 Posts

Posted - 2011-06-28 : 10:35:52

quote:
Originally posted by Peso

Please post the EXPECTED result, not the erraneous result.

N 56°04'39.26"
E 12°55'05.63"

Hi, I'm sorry, here is the expected result:


WCL_WorkOrderID		WCL_OperationSeqID	WCL_WorkCenterID

(note: no records returned)

But, importantly, the *reason* I expect no records to be returned is because WorkOrderID 50213 has records in the WOM table for OperationSeqID values that correspond with WorkCenterID values between 20 and 40 (in the WCL table).

To clarify, I want the main query to return all Work Orders that DO NOT have records in the WOM table with OperationSeqID values that correspond with WorkCenterID values between 20 and 40 (in the WCL table).

Does that make sense?

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-28 : 17:04:34

quote:
Originally posted by daylward

quote:
Originally posted by Peso

Please post the EXPECTED result, not the erraneous result.

N 56°04'39.26"
E 12°55'05.63"

Hi, I'm sorry, here is the expected result:

WCL_WorkOrderID WCL_OperationSeqID WCL_WorkCenterID

(note: no records returned)

But, importantly, the *reason* I expect no records to be returned is because WorkOrderID 50213 has records in the WOM table for OperationSeqID values that correspond with WorkCenterID values between 20 and 40 (in the WCL table).

To clarify, I want the main query to return all Work Orders that DO NOT have records in the WOM table with OperationSeqID values that correspond with WorkCenterID values between 20 and 40 (in the WCL table).

Does that make sense?

...not sure I understood what you want to accomplish, but here is my guess:[code]      select distinct WCL.WCL_WorkOrderID, WCL.WCL_OperationSeqID, WCL.WCL_WorkCenterID
      from WCL
	  where not exists 
		(select * 
                   from WOM
                   join wcl l on l.wcl_workOrderId = wom.wom_work_order_id and l.wcl_OperationSeqID = wom.wom_operationSeqId
		 where 
		 WOM.WOM_WorkOrderID = WCL.WCL_WorkOrderID and 
		 L.WCL_WorkCenterID between 20 and 40)
      and
      wcl.WCL_WorkOrderID = '50213'

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

daylward
Starting Member

5 Posts

Posted - 2011-06-28 : 17:06:40

Ok, let me try to make this a little easier to understand what I need. Let's take a little more data - 3 work orders this time. One has records in WOM with OperationSeqID 20 and 30, one has none with 20 and 30 but has other OperationSequId's, and the third has no recors in WOM:



WKO_WorkOrderID		WKO_ItemID	
50213			H3-1903-100	
51200			H3-1625-1B	
26934			H3-1806-101	

WOO_WorkOrderID	WOO_OperationSeqID	WOO_WorkCenterID
50213			10			10
50213			20			30
50213			30			20
50213			35			90
51200			10			10
51200			20			30
51200			30			20
26934			10			10
26934			20			30
26934			30			20

WOM_WorkOrderID		WOM_OperationSeqID	
50213				10	
50213				20	
50213				30	
51200				10	

WCL_WorkCenterID	WCL_WorkOrderID		WCL_OperationSeqID
10				50213			10
30				50213			20
20				50213			30
90				50213			35
10				51200			10
30				51200			20
20				51200			30
10				26934			10
30				26934			20
20				26934			30

What I need is a query that returns all WorkOrder records (from WKO or WCL or whatever table – the important part is the WorkOrderID) that have no records in the WOM table with OperationSeqID values that correspond in the WCL table to WorkCenterID’s between 20 and 40 (inclusive).

So, with the above data, the query should return WorkOrder 51200 and 26934 (because there are no records in the WOM table with OperationSeqID 30 and 20, which correspond with WorkCenterID 20 and 30 respectively according to the WCL table records for those WorkOrderID’s), but not 50213 (because it has records in the WOM table with OperationSeqID 30 and 20).

I hoped this would do the trick:


      select distinct WCL.WCL_WorkOrderID, WCL.WCL_OperationSeqID, WCL.WCL_WorkCenterID
      from WCL
          where not exists 
               (select * from WOM 
                where 
                WOM.WOM_WorkOrderID = WCL.WCL_WorkOrderID and 
                WCL.WCL_WorkCenterID between 20 and 40)

But no, it returns this:

 

WCL_WorkOrderID		WCL_OperationSeqID		 WCL_WorkCenterID
 26934				10				10
 26934				20				30
 26934				30				20
 50213				10				10
 50213				35				90
 51200				10				10

(Note it is not showing records with WorkCenterID 20 and 30, but it is showing records that have other WorkCenterIDs, whether or not they have WOM records with the (un)desired OperationSeqId values.)

It should return this:


WCL_WorkOrderID		 WCL_OperationSeqID		 WCL_WorkCenterID
 26934				10				10
 26934				20				30
 26934				30				20
 51200				10				10

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-29 : 02:55:49

Have you tried the query I posted?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-29 : 06:42:05

[code]DECLARE @WOM TABLE
(
WOM_WorkOrderID INT,
WOM_OperationSeqID INT
)

INSERT @WOM
VALUES (50213, 10),
(50213, 20),
(50213, 30)

DECLARE @WCL TABLE
(
WCL_WorkCenterID INT,
WCL_WorkOrderID INT,
WCL_OperationSeqID INT
)

INSERT @WCL
VALUES (10, 50213, 10),
(30, 50213, 20),
(20, 50213, 30),
(90, 50213, 35)

-- Peso
;WITH cteSource(WCL_WorkOrderID, WCL_OperationSeqID, WCL_WorkCenterID, Yak)
AS (
SELECT a.WCL_WorkOrderID,
a.WCL_OperationSeqID,
a.WCL_WorkCenterID,
SUM(CASE
WHEN b.WOM_OperationSeqID IS NULL THEN 0
ELSE 1
END) OVER (PARTITION BY a.WCL_WorkOrderID) AS Yak
FROM @WCL AS a
LEFT JOIN @WOM AS b ON b.WOM_WorkOrderID = a.WCL_WorkOrderID
AND b.WOM_OperationSeqID = a.WCL_OperationSeqID
AND b.WOM_OperationSeqID BETWEEN 10 AND 40
)
SELECT WCL_WorkOrderID,
WCL_OperationSeqID,
WCL_WorkCenterID
FROM cteSource
WHERE Yak = 0[/code]

N 56°04'39.26"
E 12°55'05.63"

daylward
Starting Member

5 Posts

Posted - 2011-06-29 : 10:21:53

quote:
Originally posted by mmarovic

Have you tried the query I posted?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Hi Mirko,

Sorry for the delay; I saw your post immediately after I posted my last post but I didn't have time to try it until now. At first try, it seems to be working! I will test it to make sure it's working in all cases. Thank you!

Dan

daylward
Starting Member

5 Posts

Posted - 2011-06-29 : 10:46:36

quote:
Originally posted by Peso
WITH cteSource(WCL_WorkOrderID, WCL_OperationSeqID, WCL_WorkCenterID, Yak)
AS (
SELECT a.WCL_WorkOrderID,
a.WCL_OperationSeqID,
a.WCL_WorkCenterID,
SUM(CASE
WHEN b.WOM_OperationSeqID IS NULL THEN 0
ELSE 1
END) OVER (PARTITION BY a.WCL_WorkOrderID) AS Yak
FROM @WCL AS a
LEFT JOIN @WOM AS b ON b.WOM_WorkOrderID = a.WCL_WorkOrderID
AND b.WOM_OperationSeqID = a.WCL_OperationSeqID
AND b.WOM_OperationSeqID BETWEEN 10 AND 40
)
SELECT WCL_WorkOrderID,
WCL_OperationSeqID,
WCL_WorkCenterID
FROM cteSource
WHERE Yak = 0[/code]

N 56°04'39.26"
E 12°55'05.63"

Hi Peso,

Wow, there are some concepts here I haven't ever used before here, I've gotta spend some time to fully figure this one out. It seems more complex than the mmarovic's suggestion, which seems to be working; is there a reason this one is better (e.g., more efficient)?

(BTW, it is working too - giving the same results as mmarovic's query, with the slight change of "between 20 and 40" instead of "between 10 and 40".)

Dan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-29 : 12:12:00

You will have to compare

SET STATISTICS IO ON -- Check number of reads.
SET STATISTICS TIME ON -- Check time for execution

You can also turn on the graphical execution plan to see which is more efficient.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources