当Table2没有与Table1相关的记录时，MySQL加入2个表

I have two tables, call them Table1 and Table2. Table1 has a primary key of "ID" and Table2 has a foreign key field called "Table1ID".

我有两个表,称之为Table1和Table2。 Table1的主键为“ID”,Table2的外键字段为“Table1ID”。

I can run this join, but it will only work the way I want it to when there is a matching primary and foreign key value in both tables.

我可以运行此连接,但只有在两个表中都存在匹配的主键和外键值时,它才会按照我希望的方式工作。

SELECT a.*, sum(b.Time) AS Time FROM Table1 AS a JOIN Table2 AS b ON a.ID = b.Table1ID

As you can see, I'm trying to pull all fields from Table1 and a sum of the field "Time" in Table2, where the primary and foreign keys match.

正如您所看到的,我正在尝试从Table1中提取所有字段以及Table2中字段“Time”的总和,其中主键和外键匹配。

If there isn't a foreign key, I still want the record from Table1 to display, the "Time" field should simply show a 0.

如果没有外键,我仍然希望显示Table1中的记录,“Time”字段应该只显示0。

2 个解决方案

#1

First it sounds like you need to use aggregation since you're trying to sum the times for a given id. Use group by for that and define the fields as needed (vs *).

首先,听起来你需要使用聚合,因为你试图总结给定id的时间。使用group by并根据需要定义字段(vs *)。

Second, you need to use an outer join to return those records that don't have matches.

其次,您需要使用外部联接来返回那些没有匹配项的记录。

Finally, you can use coalesce to convert null sums to 0:

最后,您可以使用coalesce将null sum转换为0:

SELECT a.id, 
    coalesce(sum(b.Time),0) AS Time 
FROM Table1 AS a 
    LEFT JOIN Table2 AS b ON a.ID = b.Table1ID
GROUP BY a.id

SQL Fiddle Demo

SQL小提琴演示

#2

Your query as written would not execute because you are not allowed to do an aggregate function in the select clause if other fields are present that are not in a group by clause. So a couple of options are:

您写入的查询将不会执行,因为如果存在不在group by子句中的其他字段,则不允许在select子句中执行聚合函数。所以有两个选择:

Include every field in Table1 into your select and group by clauses such as:

将Table1中的每个字段包含在您的select和group by子句中,例如:

SELECT a.ID, a.Attribute1, a.Attribute2, a.Attribute3...
    , coalesce(sum(b.Time), 0) AS Time 
FROM Table1 AS a 
LEFT JOIN Table2 AS b 
ON a.ID = b.Table1ID
Group by a.ID, a.Attribute1, a.Attribute2, a.Attribute3

Or you can create a sub-query that does the aggregation on on Table2 that is then joined with Table1.

或者,您可以创建一个子查询,在Table2上进行聚合,然后与Table1连接。

SELECT a.*
    , coalesce(b.Time, 0) AS Time 
FROM Table1 AS a 
LEFT JOIN 
    (
    SELECT Table1ID, SUM(Time) Time
    FROM Table2
    GROUP BY Table1ID
    ) AS b 
ON a.ID = b.Table1ID

Note that you need a LEFT JOIN, which means that every record in the first table (Table1) is returned whether or not there is a matching record in the second table. If you don't want a null in your results if there isn't a match, then you add the coalesce function to turn any nulls to zeros.

请注意,您需要LEFT JOIN,这意味着无论第二个表中是否存在匹配记录,都会返回第一个表(Table1)中的每个记录。如果您不希望在结果中出现null,如果没有匹配项,则添加coalesce函数以将任何空值转换为零。

#1