Proper SQL datatables query writing

I have a table t1 with three columns id,c1,c2,c3 with certains numbers. I have a di开发者_运维知识库fferent table t2 with id,name where values in id are values of c1,c2,c3.

I am unable to write a query where i can get results as

id | names of c1 | names of c2 | names of c3

can somebody help.

One way would be to write subqueries to retrieve the name for each column:

select  t1.id
,       (select name from t2 where t2.id = t1.c1) as C1Name
,       (select name from t2 where t2.id = t1.c2) as C2Name
,       (select name from t2 where t2.id = t1.c3) as C3Name
from    t1

Or you could use a tripe join:

select  t1.id
,       t2_1.name as C1Name
,       t2_2.name as C2Name
,       t2_3.name as C3Name
from    t1
join    t2 as t2_1 on t1.c1 = t2_1.id
join    t2 as t2_2 on t1.c2 = t2_2.id
join    t2 as t2_3 on t1.c3 = t2_3.id

You will need to join three times on each of the values to get their name.

SELECT T1.id, FirstJoin.Name, SecondJoin.Name, ThirdJoin.Name FROM T1
JOIN T2 FirstJoin On T1.c1 = FirstJoin.Id
JOIN T2 SecondJoinOn T1.c2 = SecondJoin.Id
JOIN T2 ThirdJoinOn T1.c3 = ThirdJoin.Id

I apologize for the crappy aliasing.

SELECT
    t1.id,
    sub1.name,
    sub2.name,
    sub3.name
FROM
    t1
        JOIN t2 AS sub1 ON t1.c1 = sub1.id
        JOIN t2 AS sub2 ON t1.c2 = sub2.id
        JOIN t2 AS sub3 ON t1.c3 = sub3.id;

But you should fix your datamodel, 3 columns with the same type of data, sounds like a bad datamodel. Do some normalization.