CODEWITHSUNDEEP
mysqlsqlcakephp-2.0
mickburkejnr
mickburkejnr

Reputation: 3690

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

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.

Upvotes: 1

Views: 202

Answers (4)

Vulcronos
Vulcronos

Reputation: 3456

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.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

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

Upvotes: 1

Alex
Alex

Reputation: 1593

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.

Upvotes: 0

Bandydan
Bandydan

Reputation: 631

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.

Upvotes: 0

Related Questions