December 14, 2009 at 8:48 am
All I need to do is have a column returned indicating if a record exists in a 2nd table. I care nothing about what is there, just that something is there for the idnumber I'm looking at.
The below script:
Select *,(select count(distinct idnumber) from table2 where table2.idnumber=table1.idnumber) as TRECS From (table1 left join table3 on (table3.idnumber=table1.idnumber) Where ...
works in query analyzer if my where clause will result in a single record being returned. However, if I run this in a way that multiple records are returned then it fails to function correctly.
Because of the number of fields I'd rather not abandon using the "*", but the only solution that I see working requires a GROUP BY and the "*" doesn't work there.
Any idea how I can pull this off? My alternative is to have my program perform another query for each record returned in the first one. I would rather not slow things down that much.
Any ideas?
December 14, 2009 at 9:42 am
Firstly you shouldn't be using select *, no matter the number of columns. You're risking the app possibly breaking if a column is added in the future. Just specify the columns. It just has to be typed once.
Try moving the subquery into the from clause and joining it in. Something like this (untested)
Select <column list>
ISNULL (ARowExists, 0) as TRECS
From table1
left outer table3 on (table3.idnumber=table1.idnumber)
left outer join (select idnumber, 1 as ARowExists from table2 group by idnumber) DoesRowExist
on DoesRowExist.idnumber=table1.idnumber
Where ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2009 at 10:08 am
Yep, that type of query is the only one I could get to work.
As for the "*", the reason I use it is the database is a source for downstream applications that utilize a subset of the data with all of the same fields. In the program I make sure the destination database has all the fields, create them if necessary, and then populate. It's more likely the downstream applications will break if I don't use the "*" because I then have to modify this particular program every time a new field is added or changed. I generally agree with you about the fields, but in this case the "*" works.
I guess I'll write code that builds the field list based on the source database so it can dynamically build the select fields list and use Group By ... aaarrrrrgggg.
In any case, thanks for the feedback. You've confirmed what I feared, that there's no magic bullet for this one.
Brian
December 14, 2009 at 11:54 am
bkeahl (12/14/2009)
I guess I'll write code that builds the field list based on the source database so it can dynamically build the select fields list and use Group By ... aaarrrrrgggg.
Why? Look at the query I gave you. The only group by is in the subquery and only has to be on the join column and that's only so the subquery can't return multiple rows. Distinct would do the same and be more understandable. No aaarrrrrgggg at all.
You've confirmed what I feared, that there's no magic bullet for this one.
What's wrong with the query I gave you? Does it not work? Does it not do what you want it to?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply