TSQL Help - SQL Server Forums

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

Transact-SQL (2012)

TSQL Help

Author

Topic

sc22
Starting Member

3 Posts

Posted - 2015-03-20 : 16:10:46

I have a table with the structure below. I'm looking to get all records where the SAME record in eX_ID match all the criteria in category ID.

Example: Category_ID = 203 and 204. Would return 'BAR105842'

ex_ID category_ID ID
BAR105842 203 2
BAR105842 204 3
BAR105842 210 33
BAR105842 230 89
BAR105842 234 133
FLE10311 260 736
FLE10311 261 737
FLE10311 268 1775
FLE10311 282 1776
FLE10311 298 2755
XGEN1443 315 3041
XGEN1443 343 6267
XGEN1443 342 6268
XGEN1443 345 6269
XGEN1443 341 6270
XGEN1443 380 13168
XGEN1443 383 13169
XGEN1443 412 19312

Thanks!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-20 : 16:34:43

one way:


select * from table
where category_ID in (203, 204)
and 2 = 
(
select count(*) from table
where category_ID in (203, 204)
)

sc22
Starting Member

3 Posts

Posted - 2015-03-20 : 16:52:09

Thank you for the reply!

But that did not return any records.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-20 : 16:52:59

select ex_ID, count(*)
from @t
where category_id in (203, 204)
group by ex_ID
having count(*) = 2

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sc22
Starting Member

3 Posts

Posted - 2015-03-20 : 17:05:10

Thank you that is what I needed!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-20 : 17:14:26

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources