CODEWITHSUNDEEP
mysqlsqljoin
Php Pete
Php Pete

Reputation: 762

How to link 4 tables using an mySQL query

Vehicle

+--------------------+--------------+------+-----+---------+
| Field              | Type         | Null | Key | Default | 
+--------------------+--------------+------+-----+---------+
| id                 | int(11)      | NO   | Pk  | NULL    |       
| model              | varchar(35)  | NO   |     | NULL    |            
+--------------------+--------------+------+-----+---------+

info

+--------------------+--------------+------+-----+---------+
| Field              | Type         | Null | Key | Default | 
+--------------------+--------------+------+-----+---------+
| id                 | int(11)      | NO   | Pk  | NULL    |       
| vehicle_id         | varchar(35)  | NO   | FK  | NULL    |       
| location           | varchar(35)  | NO   |     | NULL    |       
+--------------------+--------------+------+-----+---------+

Axle 1

+--------------------+--------------+------+-----+---------+
| Field              | Type         | Null | Key | Default | 
+--------------------+--------------+------+-----+---------+
| id                 | int(11)      | NO   | Pk  | NULL    |       
| vehicle_id         | varchar(35)  | NO   | FK  | NULL    |       
| weight             | varchar(35)  | NO   |     | NULL    |       
+--------------------+--------------+------+-----+---------+

Axle 2

+--------------------+--------------+------+-----+---------+
| Field              | Type         | Null | Key | Default | 
+--------------------+--------------+------+-----+---------+
| id                 | int(11)      | NO   | Pk  | NULL    |       
| vehicle_id         | varchar(35)  | NO   | FK  | NULL    |       
| weight             | varchar(35)  | NO   |     | NULL    |       
+--------------------+--------------+------+-----+---------+

I wish to create a query that will return all the fields from all tables with a common vehicle_id. The vehicle_id being a reference in each of the 3 tables (info, axle1, axle 2) to the primary key in the Vehicle table. Could someone please explain how I might go about doing so? I tried using multiple joins but it didnt work!Many thanks.

EDIT:

Query I tried was;

SELECT * 
      FROM  Vehicle
      JOIN info, axle1, axle 2
      ON vehicle.id = axle1.vehicle_id 
     AND vehicle.id = axle2.vehicle_id AND vehicle.id = info.vehicle_id

Upvotes: 0

Views: 227

Answers (2)

Bartserk
Bartserk

Reputation: 675

Check this link: mySQL get information from multiple tables in one query I think there's an example that may fit

You just need to specify an identifier for each table, so you can specify which table are you talking about when you refer to a field. Then, make sure you link all the id's as a condition so you leave out all the combinations where identifiers don't match. That would be:

SELECT * FROM Vehicle v, Info i, Axle1 a1, Axle2 a2 WHERE v.id == a1.vehicle_id AND v.id == a2.vehicle_id AND v.id == i.vehicle_id

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

Try this instead:

SELECT *
FROM Vehicle v
INNER JOIN Info   i ON          v.id = i.vehicle_id
INNER JOIN Axle1 a1 ON i.vehicle_id  = a1.vehicle_id
INNER JOIN Axle2 a2 ON a1.vehicle_id = a2.vehicle_id

Upvotes: 2

Related Questions