Page 1 of 1

All entries

Posted: Tue Dec 14, 2010 6:29 am
by ursl40
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!

Re: All entries

Posted: Tue Dec 14, 2010 8:06 am
by Darhazer
use so-called LEFT (outer) JOIN
$query = "SELECT * FROM users LEFT JOIN cars ON cars.user_id = users.id";

Re: All entries

Posted: Tue Dec 14, 2010 11:58 pm
by ursl40
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

Re: All entries

Posted: Wed Dec 15, 2010 1:53 am
by Darhazer
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