CODEWITHSUNDEEP
sql
Joshm
Joshm

Reputation: 31

SQL Multiple columns on one table joined column from other table

I've been trying to run an SQL query but haven't been having much luck. No matter what I do I can't seem to get it to run the way I need it to. I am able to get the tables to join and get the names across, but I can't get the query output I'm looking for.

I have two tables, one table has 3 columns for 1st, 2nd, and 3rd gate techs to be entered. The second table has a list of gate techs we use, the ID for the techs is the primary key.

What I'm looking for is an SQL statement that will get the the keys from the gate table. Example would be Gate Table ID 2 would get 2, 2, 1, then join with the tech table and return the tech names instead of the key.

I can one to work if I do the following, but cant get 3 separate columns with the data. 

SELECT TName
FROM TechTable
INNER JOIN GateTable
ON GateTabe.Gate1=TechTable.ID
WHERE ID = 3

-

  GateTable   
     ID Gate1 Gate2 Gate3
        1    1    2      3
        2    2    2      1
        3    4    2      1

-

TechTable
 ID TName
 1  Tech1
 2  Tech2
 3  Tech3

4 Tech4

Query Result
 ID Gate1 Gate2 Gate3
 1  Tech1 Tech2 Tech3
 2  Tech2 Tech2 Tech1
 3  Tech4 Tech2 Tech1

Upvotes: 0

Views: 64

Answers (2)

Lalithanand Barla
Lalithanand Barla

Reputation: 63

SELECT gt.ID, t1.TName as Gate1, t2.TName as Gate2, t3.TName as Gate3
FROM GateTable gt
LEFT JOIN TechTable t1
ON gt.Gate1=t1.ID
LEFT JOIN TechTable t2
ON gt.Gate2=t2.ID
LEFT JOIN TechTable t3
ON gt.Gate3=t3.ID

You can use INNER JOIN too but it gives you only mapped columns. In your example you specified Gate1 value as 4, but it is not there in TechTable. In this case, INNER JOIN skips that value. But LEFT JOIN would give you mapped values and null for the not available mapping. You can use where clause if you need specific GateID.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

join the techTable thrice, once for each different gate.

SELECT g.id,t1.TName as gate1,t2.TName as gate2,t3.TName as gate3
FROM GateTable g
INNER JOIN TechTable t1 ON g.Gate1=t1.ID
INNER JOIN TechTable t2 ON g.Gate2=t2.ID
INNER JOIN TechTable t3 ON g.Gate3=t3.ID

Upvotes: 2

Related Questions