MySQL左联接? [英] MySQL LEFT JOIN?

问题描述

我有一个包含20行的表cars(id, name).另一个表carLog(username, car, count)包含一些行，这些行计算了玩家购买的汽车(如果没有购买，则没有行)

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)

如果他们在carLog表中有一行，但是我无法正常工作，我希望我的查询返回所有20辆汽车以及额外的联接信息.

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

这将返回数百行，我希望它返回20行(每辆车一个)，如果用户名购买了该车，则返回该行中的其他信息:

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'

我不知道我是要使用GROUP BY，WHERE还是其他类型的联接！

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

推荐答案

将用户名条件从WHERE子句移动到ON子句.

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'

在JOIN已经完成时，将应用WHERE子句.这意味着，它将丢弃LEFT JOIN添加的NULL行.

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

这篇关于MySQL左联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！