Weired table design-How to pull data

Question

Hello

I have the weirdest table design I need to pull data from.Instead of creating a connection table they decided to create a many-to-many relationship and now I need to pull data from

TLInfo table
ID        First          Last        PrimaryAffiliationID              SecondaryAffiliationID
1           joe           Smith           123                                     124


Affiliation table
ID           Description
123           ABC Affiliation
124           XYZ Affiliation

I need to get the affiliation names for Joe Smith. How do I do that? I tried the following it didn't work

SELECT AF.Affiliation 

FROM Affiliation AF

JOIN TLInfo TL on AF.ID = TL.PrimaryAffiliationID 

JOIN TLINFO TL1 on AF.ID= TL1.PrimaryAffiliationID 

JOIN Affiliation AF1 on AF1.ID = TL1.SecondaryAffiliationID 

where TL.ID = 12

Thanks for the help

---

AE, MCTS

Answer 1

How about this?:

select Description
from Affiliation
join TLInfo on Affiliation.ID IN (TLInfo.PrimaryAffiliationID,TLInfo.SecondaryAffiliationID)
where TL.ID=12

Or you could just use an OR...

select Description
from Affiliation
join TLInfo on Affiliation.ID=TLInfo.PrimaryAffiliationID or Affiliation.ID=TLInfo.SecondaryAffiliationID
where TL.ID=12

---

--Brad

Answer 2

This should do it:

SELECT TL.First, TL.Last,
AF1.Description AS PrimaryAffiliation,
AF2.Description AS SecondaryAffiliation
FROM TLInfo AS TL
LEFT JOIN Affiliation AS AF1
ON TL.PrimaryAffiliationID = AF1.ID
LEFT JOIN Affiliation AS AF2
ON TL.SecondaryAffiliationID = AF2.ID
WHERE TL.ID = 1;

--
Plamen Ratchev
http://www.SQLStudio.com