'SQL Query 3 tables

I have 3 tables as follows:

TABLE A

A id |    Another field     |  
-----------------------------
1       |   Title one       |   
2       |   Another title   | 


TABLE B

B id |    A_id  (fk)        |  
-----------------------------
1       |   1               |   
2       |   2               | 


TABLE C

C id |    B_id  (fk)        |  
-----------------------------
1       |   1               |   
2       |   2               |

How can I make a single query so that I can get the records from table C that are associated with record of id 1 from table A.

mysqlsqldatabasekey


Solution 1:[1]

First add a criteria for what you need from table A.

SELECT *
FROM A
WHERE id = 1

Table B has a foreign key (B.A_id), which points to the primary key (A.id) of Table A.

So join them on those.
Use alias names for the tables.

SELECT *
FROM A AS a
JOIN B AS b ON b.A_ID = a.ID
WHERE a.ID = 1

Table C has a foreign key (C.B_id), which points to the primary key (B.id) of Table B.

So join them on those.

SELECT *
FROM A AS a
JOIN B AS b ON b.A_ID = a.ID
JOIN C AS c ON c.B_ID = b.ID
WHERE a.ID = 1

Now you can choose the fields you need from them.
Names with spaces need to be quoted. MySql quotes with backticks ``

SELECT a.ID, a.`Another field`, b.ID AS B_ID, c.ID AS C_ID
FROM A AS a
JOIN B AS b ON b.A_ID = a.ID
LEFT JOIN C AS c ON c.B_ID = b.ID
WHERE a.ID = 1

Solution 2:[2]

If you want it to be INNER JOIN , you can select this way too:

SELECT *
FROM A AS a, B AS b, C AS c 
WHERE b.A_ID = a.ID and c.B_ID = b.ID and a.ID = 1

Solution 3:[3]

Please provide your attempts. We're not here to do the work for you.

You're looking for a join.

I'll share some steps as to obtain the query you're looking for.

Go to Query Design, add your 3 tables.

Click the field name you want to link in the other tables and drag it to the same field in the other table and you'll see a line drawn between the tables.

Double click the fields you want to be presented with.

look at SQL view to see your query statement.

Some literature to help: Googled "sql join 3 tables" https://learnsql.com/blog/how-to-join-3-tables-or-more-in-sql/

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 LukStorms
Solution 2
Solution 3 SamwiseVB

Related Questions