advanced subquery

Question

Post reply

advanced subquery

bj 76771

SSC Rookie

Points: 40
More actions
April 4, 2013 at 9:47 am

#276090

I would like to get a distinct RefNumber only if this RefNumber has EntryType 0 and 2 and 11. All 3 EntryTypes must be present and no other EntryTypes must be.
RefNumber 239000 must be returned as it contains 0, 2 and 11
RefNumber 236777 must not be returnedsince it also contains a EntryType 7
RefNumber 238888 must not be returned since it lacks the EntryType 2
RefNumber EntryType EntryAmount
----------....---------....-----------
239000............0.............200
239000............0............-200
239000............2............-200
239000............2.............200
239000............11..........-145
239000............11...........145
236777............0.............162
236777............0............-162
236777............2..............-45
236777............2...............45
236777.............7..............62
236777............7..............-62
236777............11...........-162
236777............11............162
238888............0.............345
238888............0............-345
238888............11..........-245
238888............11...........245
Hope somebody can help me, and I'm very thankfull if - thanks in advance!
Benny

Viewing 10 posts - 1 through 9 (of 9 total)

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

Sean Lange SSC Guru Points: 286601 More actions · Answer 1

Like this:

select RefNumber

from YourTable

where RefNumber not in

(

Select RefNumber

from YourTable

where EntryType not in (0, 2, 11)

)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bj 76771 SSC Rookie Points: 40 More actions · Answer 2

Hi SSCrazy Eights!

THanks for your fast reply

I have just edited my post as I didn't mentioned that RefNumber must not be returned if one of the 3 EntryTypes is missing.

You query returns also RefNumber where e.g only 0 and 11 are present, and e.g only 0 and 2 is precent

Benny

Sean Lange SSC Guru Points: 286601 More actions · Answer 3

It would much better if you could post your ddl and sample data in a consumable format. (create table and insert statements)

Also, are there any more rules or is this it?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bj 76771 SSC Rookie Points: 40 More actions · Answer 4

bj 76771

SSC Rookie

Points: 40

April 4, 2013 at 10:16 am

#1603633

There is no more rules than this

Sean Lange SSC Guru Points: 286601 More actions · Answer 5

Since you are brand new around here I create the ddl and sample data for you.

if OBJECT_ID('tempdb..#Data') is not null

drop table #Data

create table #Data

(

RefNumber int,

EntryType tinyint,

EntryAmount int

)

insert #Data

select 239000,0,200 union all

select 239000,0,-200 union all

select 239000,2,-200 union all

select 239000,2,200 union all

select 239000,11,-145 union all

select 239000,11,145 union all

select 236777,0,162 union all

select 236777,0,-162 union all

select 236777,2,-45 union all

select 236777,2,45 union all

select 236777,7,62 union all

select 236777,7,-62 union all

select 236777,11,-162 union all

select 236777,11,162 union all

select 238888,0,345 union all

select 238888,0,-345 union all

select 238888,11,-245 union all

select 238888,11,245

select * from #Data

There are a number of ways this could be accomplished. Here is one of those ways.

;with HasAllThree as

(

Select RefNumber, COUNT(distinct EntryType) as MyCount

from #Data

where EntryType in (0, 2, 11)

group by RefNumber

having COUNT(distinct EntryType) = 3

)

select d.RefNumber

from #Data d

join HasAllThree h on h.RefNumber = d.RefNumber

where d.RefNumber not in

(

Select RefNumber

from #Data

where EntryType not in (0, 2, 11)

)

group by d.RefNumber

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bj 76771 SSC Rookie Points: 40 More actions · Answer 6

Hi SSCrazy Eights

Thank you so much! It is exactly what I was looking for. It is bit more advanced than I'm used to when it comes to sql

I will remember to provide create tale + data next time

Best regards

Benny

ScottPletcher SSC Guru Points: 101167 More actions · Answer 7

SELECT RefNumber

FROM #Data d

GROUP BY RefNumber

HAVING

COUNT(DISTINCT EntryType) = 3 AND

MAX(CASE WHEN EntryType NOT IN ( 0, 2, 11 ) THEN 1 ELSE 0 END) = 0

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sean Lange SSC Guru Points: 286601 More actions · Answer 8

ScottPletcher (4/4/2013)
SELECT RefNumber FROM #Data d GROUP BY RefNumber HAVING COUNT(DISTINCT EntryType) = 3 AND MAX(CASE WHEN EntryType NOT IN ( 0, 2, 11 ) THEN 1 ELSE 0 END) = 0

Nice one Scott. I knew something along these lines but it just wasn't coming to me.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101167 More actions · Answer 9

Sean Lange (4/4/2013)
ScottPletcher (4/4/2013)
SELECT RefNumber FROM #Data d GROUP BY RefNumber HAVING COUNT(DISTINCT EntryType) = 3 AND MAX(CASE WHEN EntryType NOT IN ( 0, 2, 11 ) THEN 1 ELSE 0 END) = 0
Nice one Scott. I knew something along these lines but it just wasn't coming to me.

Yeah, the query plan is rather ugly, but to me that's just a weakness of the optimizer: it should be able o better optimize that code, and resolve the query in a single pass of the table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

advanced subquery

Cookies on SQLServerCentral