Mr.Z Mr.Z - 2 months ago 7
PHP Question

LEFT JOIN of 2 tables query is Failing

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:

schema1

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

Answer

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
Comments