Retrieve all records from one table even if a related record can't be found in a related table

Question

I've an application that checks recorded waste types. Part of the system allows the user to forecast how much waste they will recycle, and in a report it will list the forecasted waste type with how much was forecast and the actual waste that's been recorded.

The way it works it out is that there is one table called forecastwaste and a table called wastestream. wastestream holds all of the data about waste types that actually have been recycled, and forecastwaste holds the waste types that have been forecast. The wastetypes table holds the name of the available wastetypes that the user can choose from.

I have this SQL Statement ($contractid contains the id of the contract):

SELECT ws.wastetype, SUM(ws.recordedweight) totalWeight, SUM(ws.percent) totalPercent, wt.id, wt.category, wt.defrecycling, fw.tonnes 
FROM wastestream ws, wastetypes wt, forecastwaste fw 
WHERE ws.contractid = ".$contractid." 
AND fw.contractid = ".$contractid." 
AND ws.wastetype = wt.id 
AND fw.wastetype = wt.id 
GROUP BY ws.wastetype

However, the problem I have is that if there is a waste type in the forecastewate table that isn't in the wastestream table the query won't display anything. I want to get it so that if no results can be found in the wastestream table, the query will still display the forecastewaste record and return 0 if it can't find anything. The current query doesn't allow this.

How can I make the query work so that it does what I need it to?

EDIT
Thanks to Bandydan I've rewritten the query so it now looks like this:

SELECT ws.wastetype, SUM(ws.recordedweight) totalWeight, SUM(ws.percent) totalPercent, wt.id, wt.category, wt.defrecycling, fw.tonnes 
FROM c1skips.wastestream ws
LEFT JOIN c1skips.wastetypes wt ON (wt.id = ws.wastetype)
INNER JOIN c1skips.forecastwaste fw ON (wt.id = fw.wastetype)
WHERE fw.contractid = '602'
AND ws.contractid = '602'
GROUP BY ws.wastetype;

I will explain what I'm trying to do a bit better too.

I have a table called forecastwaste and in that table I have the following data:

|---------------------------------|
| wastetype | tonnes | contractid |
|-----------|--------|------------|
|     1     |   10   |    602     |
|     2     |   20   |    602     |
|     3     |   50   |    602     |
|-----------|--------|------------|

This table is then used to look at the wastestream table so see how much of the material is recycled. The wastestream table looks like this:

|-----------------------------------------|
| wastetype | recordedweight | contractid |
|-----------|----------------|------------|
|     1     |       2        |     602    |
|     1     |       4        |     602    |
|     2     |      20        |     602    |
|-----------|----------------|------------|

Both tables reference the wastetype table, which identifies the number with a waste type.

With the current query, it will only return the results if they show up in the wastestream table. However, I want it so that even if there isn't a record in the wastestream table it will return 0.

EDIT 2
I've added COALESCE to my query like this:

SELECT ws.wastetype, SUM(ws.recordedweight) totalWeight, SUM(ws.percent) totalPercent, wt.id, wt.category, wt.defrecycling, 
COALESCE(ws.recordedweight, 0) tonnes 
FROM c1skips.wastestream ws
LEFT JOIN c1skips.wastetypes wt ON (wt.id = ws.wastetype)
INNER JOIN c1skips.forecastwaste fw ON (wt.id = fw.wastetype)
WHERE fw.contractid = '602'
AND ws.contractid = '602'
GROUP BY ws.wastetype;

But the results are still the same. It's going to be either the SUM(ws.recordedweight) totalWeight or the SUM(ws.percent) totalPercent that return the NULL values in the wastestream table but there will be a value in the forecaste table trying to reference them, but COALESCE won't work with that.

Consider first switching that statement into one that uses INNER JOINs to ease readability. Then it is a matter of using a LEFT JOIN. — Mosty Mostacho, Sep 13 '13 at 23:01

dnoeth · Accepted Answer · 2013-09-16T10:10:36.437

1

You need an Inner Join between forecastwaste and wastetypes plus a Left Join to wastestream:

SELECT ws.wastetype, SUM(ws.recordedweight) totalWeight, SUM(ws.percent) totalPercent, wt.id, wt.category, wt.defrecycling, fw.tonnes 
FROM c1skips.forecastwaste fw
JOIN c1skips.wastetypes wt ON (wt.id = fw.wastetype)
LEFT JOIN c1skips.wastestream ws
ON (ws.contractid = fw.contractid) AND (ws.wastetype = fw.wastetype)
WHERE fw.contractid = '602'
GROUP BY ws.wastetype;

I replaced fw.contractid = '602' with (ws.contractid = fw.contractid) as join-condition. Now there's no need to write the contractid in two places and you might run the query without WHERE to return all rows.

Edit: Changed the outer table from wastestream to forecastwaste

edited Sep 16 '13 at 10:10

answered Sep 16 '13 at 08:41

dnoeth

59,503
4
39
56

This query returns the incorrect data. If I changed the `ws.contractid` to `fw.contractid` the correct data is displayed. However, the `0` value record still isn't displayed. – mickburkejnr Sep 16 '13 at 09:27
Ok, your outer table (i.e. the table you want all rows even if there's no match) is forecastwaste. I changed the answer accordingly, hopefully it's correct now. – dnoeth Sep 16 '13 at 09:52

score 0 · Answer 2 · edited May 23 '17 at 12:20

I think you need to rewrite that one using joins, then you will be able to use LEFT JOIN. It works the way you need.

The thing I can do in some minutes looks like that:

SELECT ws.wastetype, SUM(ws.recordedweight) totalWeight, SUM(ws.percent) totalPercent, wt.id, wt.category, wt.defrecycling, fw.tonnes 
    FROM wastetypes wt
    LEFT JOIN wastestream ws ON (wt.id = ws.wastetype)
    INNER JOIN forecastwaste fw USING(wt.id = fw.wastetype)
    WHERE wt.contractid = ".$contractid." 
    GROUP BY ws.wastetype

Here I assume that you need information from wt, information about the same type, that MAYBE exists in ws and information about that type from fw(that definitely exists). I'm not sure that this is right, cause I don't have tables to check, but I wanted to show you the whole idea.

Take a look at the answers to that question and you will find a way to solve your problem.

score 0 · Answer 3 · answered Sep 16 '13 at 08:16

0

Try:

SELECT
  wt.wastetype, wt.id, wt.category, wt.defrecycling,
  SUM(ws.recordedweight) totalWeight, SUM(ws.percent) totalPercent,
  COALESCE(fw.tonnes, 0) tonnes
FROM c1skips.wastetypes wt
LEFT JOIN c1skips.wastestream ws ON (wt.id = ws.wastetype)
LEFT JOIN c1skips.forecastwaste fw ON (wt.id = fw.wastetype)
WHERE fw.contractid = '602'
AND ws.contractid = '602'
GROUP BY ws.wastetype;

Note that FROM was changed to wastetypes, wastestream is moved to LEFT JOIN and there is a function COALESCE.

answered Sep 16 '13 at 08:16

Alex

1,605
11
14

But how about other parts of query? You still have `FROM c1skips.wastestream` while it should be added using `LEFT JOIN`. You cannot have rows in result that are missing in table that is defined in `FROM` part. What is the result if you run whole query that I proposed? – Alex Sep 16 '13 at 08:49
The `wt.wastetype` was incorrect so i changed it to `ws.wastetype`, but the query displayed the same results as the query i had before. – mickburkejnr Sep 16 '13 at 09:26

score 0 · Answer 4 · answered Sep 17 '13 at 19:51

Based on your second edit I would recommend:

SELECT ws.wastetype, SUM(COALESCE(ws.recordedweight, 0)) totalWeight, SUM(COALESCE(ws.percent, 0)) totalPercent, wt.id, wt.category, wt.defrecycling, 
COALESCE(ws.recordedweight, 0) tonnes 
FROM c1skips.forecastwaste fw
LEFT JOIN c1skips.wastetypes wt ON (wt.id = fw.wastetype)
LEFT JOIN c1skips.wastestream ws ON (wt.id = ws.wastetype)
WHERE fw.contractid = '602'
AND ws.contractid = '602'
GROUP BY ws.wastetype;

That should work by using COALESCE inside the sum prevent nulls, and left joining to forecastwaste.

Retrieve all records from one table even if a related record can't be found in a related table

4 Answers4