CODEWITHSUNDEEP
phpmysql
RaomB
RaomB

Reputation: 35

Inner join with three table

I have three tables in my database: comment, landmarks, user and I want to get some attributes from each table, I try with below query but there's nothing retrived, this are my 3 tables

user (userid,firstName,lastName)
comment (userid,L_ID,review)
landmarks (L_ID,Title)

user and comment on userid and comment with landmarks on L_ID 

  $q = mysqli_query($con,"SELECT l.*,c.id,c.review,CONCAT(u.firstName,' ',u.lastName) name FROM landmarks l inner join comment c on l.L_ID on c.L_ID inner join users u on u.userid=c.userid WHERE ".(isset($userid) ? "c.userid=$userid" : (isset($landmarkid) ? "c.L_ID=$landmarkid" : "") )." ORDER BY c.id");

Upvotes: 1

Views: 47

Answers (1)

DarbyM
DarbyM

Reputation: 1203

I believe you have an error in the ON part of your "inner join comment c"

SELECT l.*,c.id,c.review,CONCAT(u.firstName,' ',u.lastName) name 
FROM landmarks l 
inner join comment c 
    on l.L_ID 
    on c.L_ID 
inner join users u 
    on u.userid=c.userid 
WHERE ".(isset($userid) ? "c.userid=$userid" : (isset($landmarkid) ? "c.L_ID=$landmarkid" : "") )." 
ORDER BY c.id

I believe what you want is

SELECT l.*,c.id,c.review,CONCAT(u.firstName,' ',u.lastName) name 
FROM landmarks l 
inner join comment c 
    on c.L_ID = l.L_ID 
inner join users u 
    on u.userid=c.userid 
WHERE ".(isset($userid) ? "c.userid=$userid" : (isset($landmarkid) ? "c.L_ID=$landmarkid" : "") )." 
ORDER BY c.id

Upvotes: 1

Related Questions