LEFT JOIN of 2 tables query is Failing

Question

I am trying to get the left outer join of 2 tables with respect to two tables , but i am unable to execute this query , phpmyadmin is giving #1064 error on line 12 when running this query:

SELECT 
pt.id as planid,
pt.trip_name,
pt.description,
cor.latitude,
cor.longitude,
bb.id as bookmarkid,
bb.num_of_persons as persons
FROM 
planned_trips as pt,
coordinates as cor,
LEFT JOIN Bookmarkedby as bb,Users as user
ON 
user.id = 1 AND
user.id = bb.user_id AND
bb.plannedtrips_id = pt.id AND
pt.coordinates_id = cor.id'

I've struggling for an hour , what am i missing?? my database schema looks like this:

I am Currently just preparing my query i need to run this query on codeIgnitor.

You can't put two table names after `LEFT JOIN`. You need a separate `LEFT JOIN` clause for each table you want to join with. — Barmar, Sep 23 '16 at 22:59
You should also use explicit `INNER JOIN` clauses for the other tables. — Barmar, Sep 23 '16 at 23:00
Problem is where to merge , INNER JOIN query with LEFT JOIN ones — Zulqurnain Jutt, Sep 23 '16 at 23:01
Why are you joining with `Users` but not using anything from that table in the `SELECT` clause? — Barmar, Sep 23 '16 at 23:03
@Drew Is that really a good duplicate? The problem in that question was a missing GROUP BY. His LEFT JOIN syntax was already correct. The title matches this one, but the actual problem doesn't. — Barmar, Sep 23 '16 at 23:10
@Barmar it shows how to left join multiple tables. Sure both didn't march into the problem arena with the same issues to start. It should not be the burden of a dupe hammer to find an exact problem match, such as we are both logging in as 'bob'. This op does not know how to left join properly — Drew, Sep 23 '16 at 23:16
@Drew Yes, it has the correct syntax in the question, but nowhere does it explain what this poster did wrong. He would have to infer his mistake. You might as well just point him to the documentation. Either way, it won't help him or future visitors who make this mistake. — Barmar, Sep 23 '16 at 23:20
i think next time i should match the question in `plagiarism` tool before posting , sorry @Drew — Zulqurnain Jutt, Sep 23 '16 at 23:20

Barmar · Accepted Answer · 2016-09-23T23:08:37.543

2

You need a separate LEFT JOIN for each table.

SELECT 
    pt.id as planid,
    pt.trip_name,
    pt.description,
    cor.latitude,
    cor.longitude,
    bb.id as bookmarkid,
    bb.num_of_persons as persons
FROM planned_trips as pt
INNER JOIN coordinates as cor ON pt.coordinates_id = cor.id
LEFT JOIN Bookmarkedby as bb ON bb.plannedtrips_id = pt.id
LEFT JOIN Users as user ON user.id = bb.user_id AND user.id = 1

edited Sep 23 '16 at 23:08

answered Sep 23 '16 at 23:01

Barmar

741,623
53
500
612

so we can do repeated separate left joining , that's new for me thanks – Zulqurnain Jutt Sep 23 '16 at 23:14

score 0 · Answer 2 · edited Sep 23 '16 at 23:02

0

Problem is in the line LEFT JOIN Bookmarkedby as bb,Users as user. Consider changing it to

FROM 
planned_trips as pt
JOIN coordinates as cor ON pt.coordinates_id = cor.id
LEFT JOIN Bookmarkedby as bb ON bb.plannedtrips_id = pt.id
LEFT JOIN Users as user ON user.id = bb.user_id 
AND user.id = 1

edited Sep 23 '16 at 23:02

Barmar

741,623
53
500
612

answered Sep 23 '16 at 23:02

Rahul

76,197
13
71
125

LEFT JOIN of 2 tables query is Failing

2 Answers2