MySQL-选择3个具有正确左连接语法的表 [英] MySQL - select 3 tables with correct left join syntax

问题描述

希望您可以帮助我正确使用SQL查询的语法(使用MySQL 5.5.25).

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

我有3张桌子:

• 数据
• data_tmp
• 用户

data表为空-具有自己的结构但没有行

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

数据:

id | name | who
----------------

data_tmp :

id | cars | who
---------------
1  | lambo| 2

用户

who | name |
------------
 2  | john

我的查询是:

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

这当然会返回一个空结果(因为data为空，所以没有适合所有参数的行).

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

我想要实现的是:

users.name | number | data_tmp.cars |
-------------------------------------
 john      |    0   | lambo         |

我确定我必须-以某种方式-使用LEFT JOIN，但是找不到正确的语法.希望你能帮助我.

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

Kalreg

推荐答案

尝试一下(不使用子查询)

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:

MSSQL服务器@ SQLFIDDLE
MYSQL @ SQLFIDDLE

MSSQL SERVER @ SQLFIDDLE
MYSQL @ SQLFIDDLE

这篇关于MySQL-选择3个具有正确左连接语法的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！