Help with SQL Query

[markronz]

Hello everyone-
I am in need of some assistance in making an SQL query that is a bit outside of my current knowledge. It involves a table with two columns, named tblDestinations. Here's some example rows:

[u]GroupName[/u]             [u]Destination[/u]
Team1                        Team1@email.com
Team2                        Team2@email.com
InvestmentsTeamWarning       BoxInvestments@email.com
InvestmentsTeamCritical      InvestmentsTeamWarning
InvestmentsTeamCritical      555-555-5555

So as you can see it's a table of destinations. The thing is the Destination column may contain other GroupNames, as is the case for InvestmentsTeamWarning. It is it's own row, and it is also a destination for InvestmentsTeamCritical. So the groups are sort of nested in this fashion.

So I'd like to create a query that makes up a third column, something named maybe "IsGroup" that is either 'yes' or 'no' depending on whether or not it appears in the GroupName column of the table. So the desired query would return results that look like this:

[u]GroupName[/u]             [u]Destination[/u]         [u]IsGroup[/u]
Team1                        Team1@email.com            No
Team2                        Team2@email.com            No
InvestmentsTeamWarning       BoxInvestments@email.com   No
InvestmentsTeamCritical      InvestmentsTeamWarning     Yes
InvestmentsTeamCritical      555-555-5555               No

So, I need help making that query.
So I obviously need Select GroupName, Destinations from tblDestinations.
Then I need to add something that checks
-if Destination IS in (Select GroupName from tblDestinations) set new IsGroup column to 'Yes'
-if Destination is NOT in (Select GroupName from tblDestinations) set new isGroup column to 'No'.

Hopefully that made sense. Could someone kindly help me form this query correctly please? Let me know if you need any more details!

 

[imex]

Hi,

Try something like this:

select 
    t.GroupName,
    t.Destination,
    case when g.Destination is null then 'No' else 'Yes' end as IsGroup
from TblDestinations as t
left join TblDestinations as g
    on g.GroupName = t.Destination

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gmmastros]

This query can be a little tricky, but certainly not impossible. The trick is to write a self join query. "Self Join" isn't part of the T-SQL language, it's more of a concept. In this case, we will join the table to itself to get the required information. When writing a self join query, it's necessary to use table aliases.

Select A.GroupName,
       A.Destination,
       Case When B.GroupName Is NULL Then 'No' Else 'Yes' End As IsGroup
From   tblDestinations As A
       Left Join tblDestinations As B
         On A.Destination = B.GroupName

Notice how one of them is aliased "As A" and the other "As B". This is required. Also notice how I join on the Destination column of 1 table to the GroupName column of the other table. I make sure to left join the tables so that I don't get just those rows that match.

Make sense?

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

 

[markronz]

That did the trick, thank you both! And yes, it makes sense. I did have to make one modification, just to be clear though, to add the DISTINCT keyword. For some reason it added a few duplicate rows. So I added it, and it now has the right number of rows. So here's my query.

Select Distinct A.GroupName,
       A.Destination,
       Case When B.GroupName Is NULL Then 'No' Else 'Yes' End As IsGroup
From   tblDestinations As A
       Left Join tblDestinations As B
         On A.Destination = B.GroupName

Thanks again!

 

[markronz]

Turns out that I had this wrong as to what I was trying to accomplish. I need to be able to query the tblDestinations table for a specific group. So let me restate what I mean.

basically this is what I want:
Select Destination, IsGroup
From tblDestinations
Where GroupName = "something I specify"

IsGroup needs to be defined as this:
IsGroup would be 'Yes' if IN "Select DISTINCT GroupName from tblDestinations
IsGroup would be 'No' if NOT IN "Select DISTINCT GroupName from tblDestinations

Here is my stab at making this query:

Select Destination, 
Case Destination IN (Select DISTINCT GroupName from tblDestinations) Then 'Yes' Else 'No' End As IsGroup 
From   tblDestinations 
Where GroupName ='something I specify' 
ORDER BY DestinationName

I tried to run something close to this in my SQL query window in Microsoft SQL Server Management Studio and it worked. But when I try to run it through my VB.NET program, it says "Error message: Incorrect syntax near the keyword 'IN'."

Any idea what it doesn't like?

 

[markronz]

Actually, I think I just figured it out. I had to add the word "WHEN".

Select Destination, 
Case Destination IN (Select DISTINCT GroupName from tblDestinations) Then 'Yes' Else 'No' End As IsGroup 
From   tblDestinations 
Where GroupName ='something I specify' 
ORDER BY DestinationName

Strange it works in the Sql Management server query window. Oh well, it appears to be working now. Thanks anyway!

 

[markronz]

oops, I meant

Select Destination, 
Case WHEN Destination IN (Select DISTINCT GroupName from tblDestinations) Then 'Yes' Else 'No' End As IsGroup 
From   tblDestinations 
Where GroupName ='something I specify' 
ORDER BY DestinationName