July 28, 2006 at 9:44 am
Is there a way to pass a value from a select into a nested query?
Something like this:
SELECT ID, Name, (SELECT total from OtherTable WHERE FirstTableID=the ID in the first part of the SELECT]) AS OTotal FROM FirstTable;
N Dunn
July 28, 2006 at 9:54 am
I'm a litte confused. In your sub-query, you have FirstTableID = ID in the first part of the SELECT. Well the ID in the first part of the SELECT is FirstTableID, so what you are saying is FirstTableID = FirstTableID.
With that said, the rest of my post is just a guess on what you meant to say. I assume that what you want is to SELECT ID and name from FirstTable and Total (as OTotal) from OtherTable where the FirstTable ID is equal to the OtherTable ID.
Look up JOINs in BOL. Instead of using a sub-query, you should be using a JOIN. Look at this example:
SELECT FT.ID,
FT.Name,
OT.Total as OTotal
FROM FirstTable FT
INNER JOIN OtherTable OT
ON FT.ID = OT.ID
Now, I used an INNER JOIN here, but if you want all FirstTable rows regardless of whether there is a OtherTable match, use an OUTER JOIN.
July 28, 2006 at 11:42 am
You probably should give more information. An INNER JOIN will work, unless you want some aggregate or something along those lines in which a one to many situation exists:
SELECT [ID], [Name], MaxValue.Zip
FROM FirstTable
INNER JOIN( SELECT [ID], MAX( Zip) AS Zip FROM OtherTable) MaxValue ON( FirstTable[ID] = MaxValue.[ID])
I wasn't born stupid - I had to study.
July 31, 2006 at 9:49 am
Try this:
SELECT ID, Name, (SELECT total from OtherTable WHERE OtherTable.FirstTableID=FirstTable.ID) AS OTotal FROM FirstTable;
That should work as long as the sub-query only returns one record. You'll need to do a sum or top or something if it might return more than one.
August 1, 2006 at 12:25 am
It's generally reomended that sub queries go in the from clause, not in the select. Sub queries in the select get evaluated for every row of the result set and can be the cause of very slow queries.
Farrell's and John's solutions are more efficient and do the same thing.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply