Easy question for someone.. - 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

General SQL Server Forums

New to SQL Server Programming

Easy question for someone..

Author

Topic

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-07-06 : 19:21:14

I'm trying to get this statement:

SELECT * FROM tests where gradesur = '2' and asa=3 and age_low < 85 and disease = 'Renl' and disease = 'CVA' and disease = 'Resp'

to bring back the data below, but it does'nt return anything:

Gradesur | asa | test | age_low | disease
2 | 2 | X-ray | 80 | CVD
2 | 2 | X-ray | 80 | Resp

It's the 2 "and disease" statements at the end that confuses it, I want it to return records that have both those diseases in them.

Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-06 : 19:27:27

SELECT * FROM tests where gradesur = '2' and asa=2 and age_low < 85 and disease in ('CVD','Resp')
to get both

select gradesur, asa, age_low
from tests
where gradesur = '2' and asa=2 and age_low < 85 and disease in ('CVD','Resp')
group by gradesur, asa, age_low
having count(*) = 2

you can join back to the original table but you know what the diseases are so there's no need.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-07-06 : 20:02:51

Spot on, that's great thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources