CODEWITHSUNDEEP
mysqlsqljoinleft-join
Juddling
Juddling

Reputation: 4690

MySQL LEFT JOIN?

I have a table cars(id, name) containing 20 rows. The other table carLog(username, car, count) contains rows which count the cars a player has bought (if there is no row if they haven't bought the car)

I want my query to return all twenty cars, and the extra join info, if they've got a row in the carLog table but I can't get it to work.

SELECT * FROM cars LEFT JOIN carLog ON cars.id=carLog.car

This is returning hundreds of rows, I want it to return 20 rows (one for each car), and the extra info in the row if the username has purchased the car:

WHERE carLog.username='Juddling'

I have no idea if I'm meant to be using GROUP BY, WHERE or another type of join!

Upvotes: -1

Views: 4583

Answers (2)

Oswald
Oswald

Reputation: 31647

Move the username condition from the WHERE clause to the ON clause.

SELECT *
FROM cars
LEFT JOIN carLog
  ON cars.id=carLog.car
     AND carLog.username='Juddling'

The WHERE clause is applied when the JOIN is already completed. This means, it will discard the NULL rows that the LEFT JOIN added.

Upvotes: 6

Guffa
Guffa

Reputation: 700212

As you are limiting the table from the outer join, you have to put the condition in the on, not the where:

select * from cars
left join carLog on cars.id = carLog.car and carlog.username = 'Juddling'

Upvotes: 1

Related Questions