For my SQL gurus out there

PackerFanatic · 04-23-2010, 10:46 AM

I am going to try and make this as generic as possible, maybe someone knows how to do it and I can incorporate it into my more complex query. Take a result set like this:

15001 0
15001 3
10004 3
11002 4
11002 4

Basically want I want to do is this - I don't want to show the record at all if the value in column B is in (1, 3). This is done with a simple "not in" statement. But I also don't want that corresponding value from column A showing up then. For instance, 15001 - since that has one record that has a value of 3 in column B, I don't want 15001 records to show up at all. I have some tried some EXISTS minus NOT EXISTS stuff that isn't working. Any suggestions for what I can try? It doesn't seem TOO hard, but I am missing something somewhere...

MIJB#19 · 04-23-2010, 10:50 AM

Sounds like you need a subselect:

select A,B from TABLE where A not in
(select A from TABLE where B in (1, 3)

or a join:

select T1.A,T1.B from TABLE as T1
join TABLE as T2 on T1.A=T2.A
where T2.B not in (1, 3)

PackerFanatic · 04-23-2010, 10:53 AM

Wow, I told you I didn't think it was too hard...I think I was just overthinking it. I think the subselect works perfectly. Thanks MIJB!

PackerFanatic · 04-23-2010, 11:22 AM

I threw that into my main query, MIJB, and that seemed to work perfectly. Thanks again!

MIJB#19 · 04-23-2010, 12:02 PM

including the missing parenthesis at the end?

04-23-2010, 10:50 AM	#2
MIJB#19 Coordinator Join Date: Oct 2000 Location: Maassluis, Zuid-Holland, Netherlands	Sounds like you need a subselect: select A,B from TABLE where A not in (select A from TABLE where B in (1, 3) or a join: select T1.A,T1.B from TABLE as T1 join TABLE as T2 on T1.A=T2.A where T2.B not in (1, 3) __________________ * 2005 Golden Scribe winner for best FOF Dynasty about IHOF's Maassluis Merchantmen * Former GM of GEFL's Houston Oilers and WOOF's Curacao Cocktail Last edited by MIJB#19 : 04-23-2010 at 10:50 AM.

04-23-2010, 10:53 AM	#3
PackerFanatic Pro Starter Join Date: Jul 2005 Location: Appleton, WI	Wow, I told you I didn't think it was too hard...I think I was just overthinking it. I think the subselect works perfectly. Thanks MIJB! __________________ Commissioner of the RNFL

04-23-2010, 11:22 AM	#4
PackerFanatic Pro Starter Join Date: Jul 2005 Location: Appleton, WI	I threw that into my main query, MIJB, and that seemed to work perfectly. Thanks again! __________________ Commissioner of the RNFL Last edited by PackerFanatic : 04-23-2010 at 11:22 AM.

04-23-2010, 12:02 PM	#5
MIJB#19 Coordinator Join Date: Oct 2000 Location: Maassluis, Zuid-Holland, Netherlands	including the missing parenthesis at the end? __________________ * 2005 Golden Scribe winner for best FOF Dynasty about IHOF's Maassluis Merchantmen * Former GM of GEFL's Houston Oilers and WOOF's Curacao Cocktail

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)