Join 2 Tables When Table2 Doesn't Have Records Relating to Table1

Question

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".

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.

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

See this question http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins. — Brian Pressler, May 07 '15 at 21:46

score 0 · Answer 1 · answered May 07 '15 at 21:48

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 *).

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:

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

score 0 · Answer 2 · answered May 08 '15 at 19:44

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:

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

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.

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.

Join 2 Tables When Table2 Doesn't Have Records Relating to Table1

2 Answers2