Hy!
I have two tables (users, cars) that are connected through a foreign key user_id in cars (= id in users - users.id).
Some users have a car selected for them (cars.user_id = users.id), but some haven't got (cars.user_id = NULL).
I was wondering, how can I get all users (id, name, surname) (with car selected or not) with SELECT? It's a bit tricky, because I need also all the data (id, name) from cars table, also in this query...
I tried with: $query = "SELECT id, name, surname, id, name FROM users, cars";
$query = "SELECT id, name, surname, id, name FROM users, cars WHERE users.id = cars.user_id";
Thanks for Your help!
All entries
Moderator: General Moderators
Re: All entries
use so-called LEFT (outer) JOIN
$query = "SELECT * FROM users LEFT JOIN cars ON cars.user_id = users.id";
$query = "SELECT * FROM users LEFT JOIN cars ON cars.user_id = users.id";
Re: All entries
Darhazer, it's working, thanks, but when all users are listed, for users without a car, their ID's are missing:
Users:
name: x // have a car (users.id = cars.user_id)
surname: y
ID: 1
name: y // doesn't have a car (cars.user_id = NULL)
surname: z
ID: // ID is missing
name: w // have a car (users.id = cars.user_id)
surname: z
ID: 3
Users:
name: x // have a car (users.id = cars.user_id)
surname: y
ID: 1
name: y // doesn't have a car (cars.user_id = NULL)
surname: z
ID: // ID is missing
name: w // have a car (users.id = cars.user_id)
surname: z
ID: 3
Re: All entries
Well, at this point you were supposed to actually read the manual on joins
They are missing because the name of the column is the same
There are several ways to fix this, here is one:
Instead of SELECT * user
SELECT users.id, users.name, users.surname, cars.id as car_id, cars.name as car_name
They are missing because the name of the column is the same
There are several ways to fix this, here is one:
Instead of SELECT * user
SELECT users.id, users.name, users.surname, cars.id as car_id, cars.name as car_name