Subquery help

[dreamer610]

I've been struggling with this for a day or so. I have a subquery in a query which pulls and displays the name of an organization from one table (tblOrgs) based on the symbol in another table (People). The problem is, that some of the symbols in People do not have corresponding entries in tblOrgs. In these situations I'd like to just display the symbol from People. Right now when a match is not found it just returns as empty. I'm sure there has to be a way to do a Case or If then else statement but I do not know the syntax. My code is below. Any help that you can offer would be greatly appreciate, thank you!

select *, (select tblOrgs.name from tblOrgs where tblOrgs.symbol = People.masterSymbol) as theName,
from People

 

[r937]

SELECT People.*
     , COALESCE(tblOrgs.name,People.masterSymbol) AS theName
  FROM People
LEFT OUTER
  JOIN tblOrgs
    ON tblOrgs.symbol = People.masterSymbol

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

Your subquery method is not the most efficient way to return this information. Instead, I would suggest you use a left join between these tables. I understand that the different types of joins can get a little confusing. Whenever you think to yourself, "I want all the rows from one table, and the matching rows from another table", that is the time to use a left join. Ex:

Select People.*,
       Coalesce(tblOrgs.Name, People.MasterSymbol) As TheName
From   People
       Left Join tblOrgs
         On People.MasterSymbol = tblOrgs.Symbol

The coalesce function will return the value of the first parameter it finds that is not NULL. When there is a match between the 2 tables, tblOrgs.Name will contain the value in the name column. When there is no match, tblOrgs.Name will be NULL (because the row does not exist), so the value in the 2nd parameter will be returned (People.MasterSymbol).

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Try [/code]
select People.*, coalesce(T.[Name], People.MasterSymbol) as TheName from People
OUTER APPLY
(select top 1 Name from tblOrgs T where People.MasterSymbol =
t.Symbol order by [Name]) T[/code]

I used OUTER APPLY instead of LEFT JOIN for the case you may have multiple names for the symbol and want to get one name only.

PluralSight Learning Library