How to find data from multiple tables in SQL?-CodePudding

Suppose we have three tables:

Table1

|     c_id          |   categories     |   
|-------------------|------------------|
|      7            |       a          |     
|      4            |       b          |     
|      3            |       c          |

Table2

|     c_id          |   dup_id     |   
|-------------------|--------------|
|       9           |     10       |     
|       5           |      3       |     
|       6           |      2       |

Table3

|     c_id          |  description |   
|-------------------|--------------|
|      22           |     xxxx     |     
|       5           |     yyyy     |     
|      11           |     zzzz     |

Suppose if some of c_id in the Table1 equal to the dup_id in Table2, then we can find out the corresponding c_id of dup_id, and use that to find description in the Table3. What will be the optimal way to do this?

Output:

|     c_id          |  description |   
|-------------------|--------------|    
|       5           |     yyyy     |

CodePudding user response：

Seems to be a straight forward join of 3 tables:

select Table2.c_id, description
from Table1
join Table2 on Table1.c_id = Table2.dup_id
join Table3 on Table2.c_id = Table3.c_id;

CodePudding user response：

I don't is this is the optimal way or not but you can do it like this:

SELECT t2.dup_id,t3.description
FROM 
Table1 t1, Table2 t2, Table3 t3
WHERE t2.dup_id = t1.c_id AND t3.c_id = t2.dup_id

Or you can do join

select t2.dup_id, t3.description
from Table1 t1
join Table2 t2 on t1.c_id = t2.dup_id
join Table3 t3 on t2.dup_id = t3.c_id