Multiple keys to column

Transact-SQL
1

Hi all,
I have two columns: tblPerson and rCou
in rCou, countries have an id, and they have readable names
example: id 20 ='Belgium'

In my tblPerson I have two fields (among others) that hold country related data.
One is the country of birth, the other is nationality.

Suppose my person is born in country 20 , but has the country 30 nationality.

Select
tblPerson .PerLastName,
tblPerson .PerFirstName,
tblPerson ].PerCouNationality,
tblPerson ].PerCouBirth,
tblPerson .PerReportNr,
rCou.CouTextBD
From
tblPerson Inner Join
rCou On rCou.CouKey = tblPerson .PerCouBirth

This gives me the record with the two countrycodes, and the readable name for the PerCouBirth code.
But I also need the readable name for the PerCouNationality code.

How do I do that?
thanks,
James

2

Sounds like homework to me. You will need to join to rcou a second time to get the Nationality country name

3

Hi,

I'm 52, my homeworkdays are long gone...

I know what I need to do, I just don't know HOW to do that.

thanks,

James

4 
FROM tblPerson P
	--LEFT JOIN if can be nulls or empty
	JOIN rCou CB
		ON P.PerCouBirth = CB.CouKey
	--LEFT JOIN if can be nulls or empty
	JOIN rCou CN
		ON P.PerCouNationality = CN.CouKey
5

ps This is bad database design, multiple nationalities etc

1 Like
6

Don't use an extra join, that will give you duplicate rows in your output. Use an inline subquery instead. I may be a bit off on the column names, but this should give you the general idea.

Select
tP.PerLastName,
tP.PerFirstName,
(SELECT rcou2.CouTextBD FROM rCou rCou2 WHERE rCou2.CouKey = tp.PerCouNationality)
AS CouNationalityText,
tP.PerCouBirth,
tP.PerReportNr,
rCou.CouTextBD
From
tblPerson tP Inner Join
rCou On rCou.CouKey = tP.PerCouBirth

2 Likes