CODEWITHSUNDEEP
phpmysqlsql
Alb M
Alb M

Reputation: 141

Multiple left joins in a query

So, i have this query right here:

$query1 = 'SELECT users.first_name,users.last_name,users.phone, matching.date, matching.points, matching.time, matching.epos_id,rbpos_epos.epos_id,rbpos_epos.location
FROM users LEFT JOIN matching WHERE users.user_id="'.$id_user.'"
AND users.user_id=matching.user_id
LEFT JOIN rbpos_epos WHERE rbpos_epos.epos_id=matching.epos_id';

Which is giving me an error, what i'm trying to do in this last line:

 LEFT JOIN rbpos_epos WHERE rbpos_epos.epos_id=matching.epos_id';

is to get the location for that specific epos_id.

It means , in the matching table, i have a epos_id column, and in the rbpos_epos column i have an epos_id field which beside has a location field.. i need to get the respective location for each epos_id..

What am i doing wrong? Thanks..

This one works 

$query1 = 'SELECT users.first_name,users.last_name,users.phone, matching.date, matching.points, matching.time,matching.location
FROM users ,matching WHERE users.user_id="'.$id_user.'"
AND users.user_id=matching.user_id';

I need to get the location from rbpos_epos table. The two tables rbpos_epos and matching have both a epos_id field

So what i need is to find the location in rbpos_epos based on the epos_id i have at matching..

Upvotes: 0

Views: 122

Answers (2)

beerwin
beerwin

Reputation: 10327

Use ON instead of WHERE in your LEFT JOIN:

$query1 = 'SELECT users.first_name,users.last_name,users.phone, matching.date, matching.points, matching.time, matching.epos_id,rbpos_epos.epos_id,rbpos_epos.location
FROM users LEFT JOIN matching ON users.user_id="'.$id_user.'"
AND users.user_id=matching.user_id
LEFT JOIN rbpos_epos ON rbpos_epos.epos_id=matching.epos_id';

And put any WHERE clauses after the LEFT JOINs

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

Try this ::

SELECT users.first_name,users.last_name,users.phone, matching.date, matching.points, matching.time, matching.epos_id,rbpos_epos.epos_id,rbpos_epos.location
FROM users 
INNER JOIN matching ON  users.user_id=matching.user_id
INNER JOIN rbpos_epos ON rbpos_epos.epos_id=matching.epos_id
WHERE users.user_id="'.$id_user.'"

Upvotes: 3

Related Questions