Embedded Select Distinct query

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

Embedded Select Distinct query

Author

Topic

macdca44
Starting Member

10 Posts

Posted - 2015-02-18 : 08:51:23

This is part of a query I have. I want to display the diagnosis code attached to a patient. Sometimes there are more than one which is why I have tried distinct. I am getting the message 'subquery returned more than 1 value...'

If I use MIN or MAX, its giving me the min possible diagnosis code alphabetically or the max, not the one actially assigned to the patient which is what I want. Can anyone help??

(SELECT DISTINCT (Diagnosis.DiagnosisCode) FROM Diagnosis
LEFT OUTER JOIN CourseDiagnosis ON CourseDiagnosis.DiagnosisSer = Diagnosis.DiagnosisSer
LEFT JOIN Course ON Course.CourseSer = CourseDiagnosis.CourseSer
JOIN Patient ON Patient.PatientSer = Course.PatientSer
WHERE Diagnosis.ObjectStatus = 'Active' AND
Diagnosis.Description not like 'ERROR%' AND
Diagnosis.DiagnosisTableName not in ('RTDS Dates')),

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 09:52:57

I'm guessing you have something like:

where x = ( your subquery )

If so, you have to be sure that your subquery can only return one value. SQL cannot compare multiple values to a single column. DISTINCT won't help you if there are two or more DISTINCT values.

if you just run the subquery on its own, do you only ever get one value back?

macdca44
Starting Member

10 Posts

Posted - 2015-02-18 : 09:59:32

Yes, I only want one value back for each row. As I have t use an outer join I want duplicate rows for where there is a diagnosis and the duplicates are blank, and for where there is no diagnosis value I want blank rows only.

Like this

Patient Diag
1 C1
1 C1
1
1
2
2

macdca44
Starting Member

10 Posts

Posted - 2015-02-18 : 10:00:44

The problem is its giving me any code from the diagnosis table, and I want only the diagnosis for the patient vis the course link (if it sthere)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 10:06:40

Do you have something like:

where x = ( your subquery )

Does that subquery ever return more than one value?

macdca44
Starting Member

10 Posts

Posted - 2015-02-18 : 10:47:52

Yes sometimes it does

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 10:52:11

then you need to work out what you want to do for that case (depends on your data, which you know about). The subquery in

where x = ( your subquery )

cannot return more than one value, or you'll get the error you reported. Think about it, what else could SQL do? It can't just choose one value at random or treat the where like

where x = all(your subquery)

macdca44
Starting Member

10 Posts

Posted - 2015-02-19 : 10:39:07

I tried where x = all but I get the same thing

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 10:45:59

I wasn't suggesting you actually use that! What I said was, "SQL can't just choose one value at random or treat the where like where x = all(your subquery)"

My point is you need to determine what you want "x" to be compared to in the WHERE clause. I don't think that is defined completely.

However, if you are interested in ALL():


select 42
where 42 = all(select * from (values (42),(42),(42)) v(m))

returns 42. however,


select 42
where 42 = all(select * from (values (42),(42),(43)) v(m))

returns nothing.

macdca44
Starting Member

10 Posts

Posted - 2015-02-19 : 11:18:58

Im not sure what you mean by 42?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 11:24:09

42 is "the answer to life, the universe and everything"

http://en.wikipedia.org/wiki/42_%28number%29

Here, I just use it as an example for you to see how ALL(...) works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources