quote:

---

SELECT *
FROM Invite i
LEFT JOIN Guests g1
ON i.InviteID = g1.InviteID
LEFT JOIN Guests g2
ON i.InviteID = g2.InviteID
WHERE g1.Type = 'A' AND g2.Type = 'S'

---

select i.InviteID, i.InviteName, g1.Name AName, g1.GuestID AGuestID, g2.Name SName, g2.GuestID SGuestID 
from invite i
left join guests g1 on g1.inviteid = i.inviteid and g1.type = 'A'
left join guests g2 on g2.inviteid = i.inviteid and g2.type = 'S'

USE Northwind
GO

CREATE TABLE xInvite (InviteId int, InvitationDate datetime)
CREATE TABLE xGuests (GuestId int, InviteId int, GuestName varchar(25), GuestType char(1))
GO

INSERT INTO xInvite (InviteId, InvitationDate) 
SELECT 1, GetDate()

INSERT INTO xGuests (InviteId, GuestId, GuestName,GuestType)
SELECT 1,1,'Brett','S' UNION ALL
SELECT 1,1,'Brett','A' UNION ALL
SELECT 1,2,'Tara','S' UNION ALL
SELECT 1,3,'Jeff','S' UNION ALL
SELECT 1,4,'Bill','A' UNION ALL
SELECT 1,5,'Damain','A' UNION ALL
SELECT 1,6,'Rob','A' UNION ALL
SELECT 1,7,'Arnold','A' UNION ALL
SELECT 1,8,'Nigel','A'
GO
 
-- WRONG...I'm having a bad day
   SELECT *
     FROM xInvite i
LEFT JOIN xGuests g1
       ON i.InviteID = g1.InviteID
LEFT JOIN xGuests g2
       ON i.InviteID = g2.InviteID
    WHERE g1.GuestType = 'A' AND g2.GuestType = 'S'


-- But huh...what am I doing wrong?
   SELECT *
     FROM xInvite i
LEFT JOIN xGuests g1
       ON i.InviteID = g1.InviteID
      AND g1.GuestType = 'A'
LEFT JOIN xGuests g2
       ON i.InviteID = g2.InviteID
      AND g2.GuestType = 'S'
GO

DROP TABLE xInvite
DROP TABLE xGuests
GO

   SELECT *
     FROM xInvite i
LEFT JOIN xGuests g1
       ON i.InviteID = g1.InviteID
      AND g1.GuestType = 'A'

   SELECT *
     FROM xInvite i
LEFT JOIN xGuests g2
       ON i.InviteID = g2.InviteID
      AND g2.GuestType = 'S'
GO