Joining Two Tables - Twice

1

I'm new to sql, and to here, so if this is in the wrong place or I'm not explaining myself well, I apologise.

I have a simple problem, but as I'm fairly new to sql I have no idea how to fix it. Also, the tables are not mine, I'm just working with them, so I can't change any of the data and the layout is nothing to do with me. Finally, the codes used are not the actual codes, I just needed examples.

Basically there are 2 tables: Client and Header within a database called dboClientInfo.

The Client table contains 5 columns: Name, Age, DoB, Gender, Ethnicity. The Header table has 2 columns: Code and Description.

In the Client table, instead of exact data for Gender and Ethnicity codes are used. So, for example, for Gender the codes are as follows:

Male - 12345A
Female - 12345B
Not Specified - 12345C

Ethnicity codes are similar:

White British - ABCDE1
Asian: Indian - ABCDE2
Asian: Pakistani - ABCDE3
etc

I don't know why this is (as I said, it's not my data). The data looks like this:

Name Age DoB Gender Ethnicity
John Smith 18 01/01/2004 12345A ABCDE1
Jo Bakshi 20 01/01/2002 12345B ABCDE2

In the Header table the Code column contains all of the Gender and Ethnicity codes (mixed together with a bunch of other codes) and the Description column contains the description for the code, so it looks a bit like this:

12345A Male
12345B Female
12345C Not Specified
BBB710 Building1
BBB720 Building 2
ABCDE1 White British
CC6CC7 VehicleAlpha
etc

I just want to be able to join the 2 tables together so that actual Gender and Ethnicity is displayed alongside Name, Age, DoB, but I'm having trouble.

I've managed to link them together by matching the Header>Code column with the Gender and Ethnicity columns with 2 joins, but I'm getting 2 rows per client in my results that show identical information. I've tried 'Select Distinct' but it doesn't help. Could someone give me the basic code so that I can pull together a table that looks like this:

Name Age DoB Gender Ethnicity
John Smith 18 01/01/2004 Male White British
Jo Bakshi 20 01/01/2002 Female Asian: Indian

I'm learning, I'm just not a particularly fast learner.

I'm using SQL Server Management Studio v18.9.1 if that makes a difference.

2

You need to use aliases to tell one join from the other:


SELECT C.Name, C.Age, C.DoB, hg.Description AS Gender, he.Description AS Ethnicity
FROM Client c
INNER JOIN Header hg ON hg.Code = C.Gender
INNER JOIN Header he ON he.Code = C.Ethnicity
1 Like
3

Absolutely brilliant! Thanks, that does exactly what I need.