Correlated subquery with derived column

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

Transact-SQL (2005)

Correlated subquery with derived column

Author

Topic

schmidtg
Starting Member

36 Posts

Posted - 2007-09-19 : 20:04:31

I'm trying to do something like this:

SELECT p.ID, (SELECT Name FROM Names WHERE ID=p.ID) AS Name
FROM People p
WHERE p.ID IN (SELECT ID FROM Members m WHERE m.Name=Name)

where that last "Name" should refer back to the Name derived from the first subquery in the SELECT. Is there a way to assign that derived column a table prefix so it can be referenced in a later subquery?
Yes, there is a reason we're not just joining on Names, and this is a very simplified example.
Thanks for any help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-19 : 20:07:33

why don't you use INNER JOIN ?

SELECT p.ID, n.Name
FROM   People p INNER JOIN Names n
       ON     n.ID = p.ID
       INNER JOIN Members m
       ON     m.Name = n.Name

KH
[spoiler]Time is always against us[/spoiler]

schmidtg
Starting Member

36 Posts

Posted - 2007-09-19 : 20:21:35

Performance reasons mostly.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-19 : 20:37:10

try both query and check the execution plan see which gives you better performance

KH
[spoiler]Time is always against us[/spoiler]

schmidtg
Starting Member

36 Posts

Posted - 2007-09-19 : 20:38:51

I have done this extensively already. I'm just wondering if it's possible to get the above query working using the subqueries. Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-19 : 21:12:27

Schmidtg -- you are much, much better off with a JOIN. Easier to write, to read, to work with, and it performs equally as well and in many cases better than a correlated subquery. Don't try to avoid JOINS when writing relational database queries in SQL!

- Jeff
http://weblogs.sqlteam.com/JeffS

schmidtg
Starting Member

36 Posts

Posted - 2007-09-19 : 22:58:46

Ok, then tell me if you can construct a query with joins that will take care of this scenario:

We have three tables:
Entities (EntityID)
Addresses (AddressID, EntityID, AddressTypeID)
AddressTypes (AddressTypeID)

I need to join so that all entities will be returned once for each address type, including a row for address types that don’t exist for the entity.

A sample result set might look something like this:

EntityID AddressID AddressTypeID
1 1 1
1 NULL 2
2 NULL 1
2 NULL 2
3 2 1
3 3 2

Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-20 : 07:16:35

With JOINS, it is very easy:


select 
  e.entityId, a.AddressId, t.AddressTypeId
from 
  Entities e
cross join 
  AddressTypes t
left outer join 
  Addresses a on a.AddressTypeId=t.AddressTypeId and a.EntityId=e.EntityId

How would you write this with correlated sub queries?

And, of course, since we used joins, we can easily include as many columns from any of the involved tables in the result set, whereas when using a sub-query we can only select one column at a time.

Don't try to "avoid joins" when writing SQL, embrace them and learn them! That is like working with mathematics but "avoiding addition"! Pretty much the entire point of a relational database is to join and relate tables efficiently.

- Jeff
http://weblogs.sqlteam.com/JeffS

schmidtg
Starting Member

36 Posts

Posted - 2007-09-20 : 11:05:35

Thanks, I'll see if that will work for us.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources