mysql 左关联语法,MySQL-选择3个具有正确左连接语法的表

Hope you can help me with correct syntax of a SQL query (using MySQL 5.5.25).

I have 3 tables:

data

data_tmp

users

data table is empty - has it's own structure but no rows

data:

id | name | who

----------------

data_tmp:

id | cars | who

---------------

1 | lambo| 2

users

who | name |

------------

2 | john

My query is:

SELECT DISTINCT

users.name,

(SELECT count(id) FROM data WHERE who = 1) as number,

data_tmp.cars

FROM

users, data, data_tmp

WHERE

users.who = 2

AND data_tmp.who = 2

AND data.who = 2

This of course returns an empty result (there is no row that suits to all parameters because data is empty).

What I would like to achieve is:

users.name | number | data_tmp.cars |

-------------------------------------

john | 0 | lambo |

I am sure I have to - in some way - use LEFT JOIN but can't find correct syntax. Hope you can help me.

Kalreg

解决方案

give this a try (without using subquery)

SELECT a.name, b.cars, count(c.id) as number

FROM users a

INNER JOIN data_tmp b

on a.who = b.who

LEFT JOIN data c

on a.who = c.who AND

a.name = c.name

WHERE a.who = 2

GROUP BY a.name, b.cars

this works on different servers: