CODEWITHSUNDEEP
mysqljoin
Nilkamal Gotarne
Nilkamal Gotarne

Reputation: 395

MYSQL left join not working

I have three (3) tables:

I want to get all vehicles from vehicle table and firstname and last name from userInfo table if a vehicle is mapped to a user in userCarMapping table.

I wrote this query: 

SELECT `vehicle`.`vehicleId`, `vehicle`.`modelId`, `vehicle`.`RegNo`, `userInfo`.`firstName`, `userInfo`.`lastName` 
FROM `vehicle` 
LEFT JOIN `userCarMapping` ON `vehicle`.`vehicleId` = `userCarMapping`.`vehicleId` 
LEFT JOIN `userInfo` ON `userCarMapping`.`userId` = `userInfo`.`userId` 
WHERE `vendorUserId` = '1' AND `vehicle`.`status` = 'approved' AND `userInfo`.`status` = 'approved'`

and I'm getting this output where as I want all the vehicles from vehicle table.

output

Upvotes: 1

Views: 2309

Answers (2)

chirag satapara
chirag satapara

Reputation: 1947

When you use userinfo table condition in where then it only return those rows which satisfy the condition.But if you use that userinfo condition in join instead of where , then table join rows which satisfy the condition like it there is 5 records in userinfo table and records with status active only two then join consider only this 2 rows for the join.

Try below query:

 SELECT `vehicle`.`vehicleId`, `vehicle`.`modelId`, `vehicle`.`RegNo`, `userInfo`.`firstName`, `userInfo`.`lastName` 
 FROM `vehicle` 
 LEFT JOIN `userCarMapping` ON `vehicle`.`vehicleId` = `userCarMapping`.`vehicleId` 
 LEFT JOIN `userInfo` ON `userCarMapping`.`userId` = `userInfo`.`userId`  AND `userInfo`.`status` = 'approved'`
 WHERE `vendorUserId` = '1' AND `vehicle`.`status` = 'approved'

Hope you understand the answer.

Upvotes: 2

Crawdingle
Crawdingle

Reputation: 799

I believe your issue lies in the WHERE of your query. 

SELECT
    vehicle.vehicleId,
    vehicle.modelId,
    vehicle.RegNo,
    userInfo.firstName,
    userInfo.lastName
FROM
    vehicle
LEFT JOIN userCarMapping ON vehicle.vehicleId=userCarMapping.vehicleId
LEFT JOIN userInfo ON  serCarMapping.userId=userInfo.userId
WHERE vendorUserId= '1' 
AND vehicle.status= 'approved' 
AND userInfo.status= 'approved'

Basically, if a user doesn't exist for a vehicle it's still looking for the userInfo table with an approved. If you remove 

AND userInfo.status= 'approved'

Do you get the information you want?

Upvotes: 1

Related Questions