Can't figure this query out...

Question

Post reply

Can't figure this query out...

Sean Grebey-262535

SSCommitted

Points: 1724
More actions
November 26, 2008 at 8:45 am

#126165

...if any of you gurus way better at SQL than I am has a few minutes could you help me with this statement?
I have a database with 5 relevant rows:
POLE_ID, DATE1, DATE2, DIVISION, IGNORE
Sample data would look like:
1,1/1/2008,NULL,'E',0
1,1/2/2008,NULL,'E',0
1,NULL,NULL,'E',1
2,1/1/2008,NULL,'N',0
3,1/1/2008,NULL,'E',0
3,NULL,NULL,'E',0
4,1/1/2008,NULL,'E',1
I need to get a list of POLE_IDs WHERE for every record with that POLE_ID DATE1 is not NULL, DATE2 is NULL, and DIVISION = 'E'....Ignoring records where IGNORE= 1
So in the above data only POLE_ID 1 would match the criteria.
I just can't get my brain around it. The dataset is small, probably 1000 records or so, so I was trying to do a NOT IN on a subquery where I searched for one of the criteria to fail, but the IGNORE is messing me up.
SELECT * FROM MYTABLE
WHERE POLE_ID NOT IN
(SELECT DISTINCT POLE_ID FROM MYTABLE L
WHERE L.DATE1 IS NULL OR L.DATE2 IS NOT NULL OR L.Division <> 'E' OR L.IGNORE = 1)
Anybody have any suggestion on how to do this it would be greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Here is an option:

[font="Courier New"]CREATE TABLE #tmp

(POLE_ID INT, DATE1 DATETIME, DATE2 DATETIME, DIVISION CHAR(1), IGNORE BIT)

INSERT #tmp VALUES (1,'1/1/2008',NULL,'E',0)

INSERT #tmp VALUES (1,1/2/2008,NULL,'E',0)

INSERT #tmp VALUES (1,NULL,NULL,'E',1)

INSERT #tmp VALUES (2,1/1/2008,NULL,'N',0)

INSERT #tmp VALUES (3,1/1/2008,NULL,'E',0)

INSERT #tmp VALUES (3,NULL,NULL,'E',0)

INSERT #tmp VALUES (4,1/1/2008,NULL,'E',1)

SELECT

DISTINCT T.POLE_ID

FROM

#tmp T

LEFT JOIN #tmp TI ON TI.POLE_ID = T.POLE_ID

AND (TI.DATE1 IS NULL

OR TI.DATE2 IS NOT NULL

OR TI.DIVISION <> 'E')

AND TI.IGNORE = 0

WHERE

T.IGNORE = 0

AND TI.POLE_ID IS NULL[/font]

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

SELECT * FROM mytable

WHERE POLE_ID NOT IN

(SELECT DISTINCT POLE_ID FROM mytable L

WHERE (L.DATE1 IS NULL OR L.DATE2 IS NOT NULL OR L.DIVISION <> 'E') AND L.IGNORE = 0)

AND IGNORE = 0

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 3

Mark (11/26/2008)
SELECT * FROM mytable WHERE POLE_ID NOT IN (SELECT DISTINCT POLE_ID FROM mytable L WHERE (L.DATE1 IS NULL OR L.DATE2 IS NOT NULL OR L.DIVISION <> 'E') AND L.IGNORE = 0)
AND IGNORE = 0

That did it. Thanks a lot for the help.