We help IT Professionals succeed at work.

query / sproc, SELECTING FROM a SELECT statement with JOINs

SIMPLIFIED!

7Teams TABLE col ParentTeamID has multiple entries of same ParentTeamID
Contacts TABLE col ParentTeamID, FirstName, etc...

Current Solution below gives the correct result:

*a VIEW named vDistinctTeams
-SELECT DISTINCT ParentTeamID
-FROM         dbo.[7Teams]

*then
SELECT     dbo.vDistinctTeams.ParentTeamID, dbo.Contacts.FirstName
FROM         dbo.vDistinctTeams LEFT OUTER JOIN
                      dbo.Contacts ON dbo.vDistinctTeams.ParentTeamID = dbo.Contacts.ContactID

*the above yields 1 row for each unique ParentTeamID in  the 7Teams Table  and its Name (and any other fields I need) from the Contacts Table

PROBLEM:  I need to do this in 1 Query (Sproc)  (I am new to db) like below...

SELECT ParentID, FirstName, etc
FROM   SELECT DISTINCT ParentTeamID
              FROM 7Teams   ??JOIN??   Contacts


Thanks in advance!
Sam
Comment
Watch Question

Senior Software Engineer
Commented:
In theory, that should do:
SELECT     distinct dbo.7Teams.ParentTeamID, dbo.Contacts.FirstName
FROM         dbo.7Teams LEFT OUTER JOIN
                      dbo.Contacts ON dbo.7Teams.ParentTeamID = dbo.Contacts.ContactID

Open in new window


or this:
SELECT     a.ParentTeamID, dbo.Contacts.FirstName
FROM         (SELECT DISTINCT ParentTeamID FROM dbo.[7Teams])  a LEFT OUTER JOIN
                      dbo.Contacts ON a.ParentTeamID = dbo.Contacts.ContactID

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Why do you even need to join with 7Teams table?
You have all information in the Contacts table.
SELECT ContactID, FirstName
FROM dbo.Contacts

Open in new window

Or do you need any particular field from 7Teams table?

Author

Commented:
Great, the second was used effectively, thanks for the simplified answer!