nested select (SQL Server)

Question

I'm not sure exactly how to get this.

What I have is something like this.

TableA:  VisitID, PersonID1, PersonID2
TableB:  PersonID, FirstName, LastName

I'd like to return Visit ID, name of PersonID1, name of personID2.

Usually I would just do a simple join, but I can't seem to get that to work

It should go something like

SELECT A.VisitID, B.FirstName+B.LastName, B.FirstName+B.LastName (this is dup, wont work)
FROM TableA A
JOIN TableB B ON TableA.PersonID1=TableB.PersonID OR TableA.PersonID2=TableB.PersonID

Thanks!

vlad · Accepted Answer · 2011-03-16 17:02:03Z

4

select A.VisitID, B1.FirstName+B1.LastName, B2.FirstName+B2.LastName
from TableA A
    join TableB B1 on A.PersonID1 = B1.PersonId
    join TableB B2 on A.PersonID2 = B2.PersonId

essentially, you join with TableB twice, once per foreign key in TableA

answered Mar 16, 2011 at 17:02

vlad

4,8082 gold badges32 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Blindy · Accepted Answer · 2011-03-16 17:02:32Z

3

You have to join tableb twice, once for each id:

select VisitID, b1.FirstName, b1.LastName, b2.FirstName, b2.LastName
from TableA
        inner join TableB b1 on TableA.PersonID1=b1.PersonID
        inner join TableB b2 on TableA.PersonID2=b2.PersonID

answered Mar 16, 2011 at 17:02

Blindy

68k10 gold badges96 silver badges141 bronze badges

1 Comment

Rob Over a year ago

This works great (as do the other answers), Thanks very much!

Sachin Shanbhag · Accepted Answer · 2011-03-16 17:04:21Z

2

I think simple inner join two times should do the trick.

select A.VisitID, (BB.FirstName + ' ' + BB.LastName) as Person1, 
(BC.FirstName + ' ' + BC.LastName) as Person2
from TableA A
inner join TableB BB on A.PersonID1 = BB.PersonId
inner join TableB BC on A.PersonID2 = BC.PersonId

answered Mar 16, 2011 at 17:04

Sachin Shanbhag

55.7k11 gold badges92 silver badges103 bronze badges

Comments

amit_g · Accepted Answer · 2011-03-16 17:04:47Z

1

If PersonID1/PersonID2 are nullable, the join has to be left join

Select
      A.VisitID
    , B1.FirstName + ' ' + B1.LastName
    , B2.FirstName + ' ' + B2.LastName
From
    TableA A
Left Join
    TableB B1
On
    A.PersonID1 = B1.PersonID
Left Join
    TableB B2
On
    A.PersonID1 = B2.PersonID

answered Mar 16, 2011 at 17:04

amit_g

31.3k8 gold badges67 silver badges124 bronze badges

1 Comment

Blindy Over a year ago

Yes but if personid1/2 are nullable, then maybe this approach isn't the best anyway. An ID linking to a list of person ID's would be better.

Collectives™ on Stack Overflow

nested select (SQL Server)

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related