11

A download is comprised of download-times, download-time id, and buno ID. Faults are comprised of fault-codes, download-time id, status, and type. A download can have many faults, and can be joined on the download-time id.

Given a set of fault-codes, the results must contain each fault-code with a corresponding fault-count. If a fault-code is not found in the download, the fault-code must be returned with a fault-count of zero.

The problem seems to require an OUTER JOIN, but haven't seen this working as expected on Postgres as it does not seem to return the set with nulls from the LEFT table.

The query is below, with some details left out for brevity:

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount 
FROM    download_time d 
LEFT OUTER JOIN fs_fault f ON f.downloadtimeid = d.id
    AND f.faultcode IN (1000,1100)
    AND f.statusid IN(2, 4)
WHERE (d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

The following day, I've edited to show the answer. All answers were close and had various elements of assistance. However, JayC's answer was closest. Here is the final SQL, having the only change as the WHERE clause taking the fault-code IN statement:

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount
FROM    download_time d  
RIGHT OUTER JOIN fs_fault f ON f.downloadtimeid = d.id
        AND f.statusid IN(2, 4)
        AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012'
        AND d.bunoid = 166501
WHERE f.faultcode IN (1000,1100)
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

Thanks, all for your assistance! Love this site!

Improve this question
2
  • 1
    Have you analyzed your data? I doubt it is issue within PostgreSQL… Can you provide table structure and sample data in SQL Fiddle, please? Commented May 14, 2012 at 20:13
  • 1
    download_time d LEFT OUTER JOIN fs_fault f ON f.downloadtimeid = d.id has download_time as the left table, not fs_fault. The join condition has nothing to do with which table is left or right in the join. Commented May 14, 2012 at 23:50

4 Answers 4

Reset to default
23

I'm giving my answer because I have significant doubts about the other answers. You gotta be careful about filter requirements. Remember, the where clause runs after your joins. So if there are any filter requirements in the where clause that refer to the non-outer joined table, you have (in many circumstances) nullified your outer join. So taking your sql, It seems the simplest solution is to either use the proper join or move the table names appropriately, and then move the filter conditions out of the where clause and into the join clause.

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount 
FROM    download_time d 
RIGHT OUTER JOIN fs_fault f ON 
    f.downloadtimeid = d.id
    AND f.faultcode IN (1000,1100)
    AND f.statusid IN(2, 4)
    AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

Another way which I believe should be equivalent is

SELECT  f.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount 
FROM    download_time d 
RIGHT OUTER JOIN fs_fault f ON 
    f.downloadtimeid = d.id
    AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
WHERE
    f.faultcode IN (1000,1100)
    AND f.statusid IN(2, 4)
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

As it doesn't strictly matter where the filter requirements on fs_fault are. (and your SQL engine's going to change that all up anyway).

Edit: Here's a SQLFiddle demonstrating filtering on the join clause vs. the where clause.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You're right about about the WHERE clause versus JOIN conditions. I fixed my answer in that regard. I don't think you've addressed his question about how to get the desired faultcodes to show even if none occur, though.
Thank you all: I credited a vote to all the respondants, as I learned something from each. However, JayC, yours was closest. The only change was to move the 'f.faultcode IN (1000,1100)' to the where clause. On doing that, the correct fault-codes are displayed. Thanks, JayC!
I couldn't get the SQLFiddle to work. I'm using IE 7.0.5730.13CO, since I'm at work. I'll have to try it at home where I usually run Chrome.
2

This will require a RIGHT OUTER JOIN. The right outer join includes all values from the right table, with NULLs where there is no entry in the left table (I'm not sure if this will work with GROUP BY, though...) if fs_fault were a table with all fault codes.

In your case, fs_fault seems to contain all faults for a download. Might this be the case for the unexpected behavior?

Improve this answer

Comments

2

If you want counts by faultcode, this seems like the simplest solution:

WITH fc(faultcode) AS (VALUES (1000,1100))
SELECT fc.faultcode, count(d.downloadtimeid) as faultcount 
  FROM fc
  LEFT JOIN (fs_fault f ON f.faultcode = fc.faultcode
                       AND f.statusid IN(2, 4)
  JOIN download_time d ON d.id = f.downloadtimeid
                      AND d.bunoid = 166501
                      AND d.downloadtime::date BETWEEN date '2011-04-11'
                                                   AND date '2011-05-01')
  GROUP BY fc.faultcode
  ORDER BY fc.faultcode

Note that I kept your conditions, where faults are not counted if they don't have the right statusid or bunoid. I was a bit afraid that the date selection might not have been doing what you thought, so I suggested an alternative. Even that might not do what you want if you're using TIMESTAMP WITHOUT TIME ZONE, but that's another story. I also added an ORDER BY clause, since you probably don't want the results in inconsistent order; without that clause it may or may not be in GROUP BY sequence, and that might change without warning.

Improve this answer

3 Comments

If a faultcode has no associated download time, it will be filtered out by the where clause.
Oops. Fixed by adding parentheses to force the order of evaluation of the joins. Thanks!
@JayC made a good point about WHERE clause versus JOIN conditions in his answer. As it had been written, if the only rows for a particular faultcode were excluded by the conditions, the faultocde would not appear. Fixed by moving the conditions.
1

The left outer join selects everything in the first table plus matching rows in the second table. The first table seems to consist of download attempts. So, your result from the "from" includes all download attempts.

But, it does not necessarily contain all your fault codes. What is happening is that you have no faults for one or more codes that meet the criteria.

You need a table that contains all the fault codes, in order for this to work. Here I just create a list of the fault codes as the first table. I think the following query does this:

SELECT thefaults.faultcode, f.downloadtimeid, d.downloadtime, count(*) as faultcount
FROM  (select 1000 as faultcode union all select 1100
      ) thefaults join
      fs_fault f
      on f.faultcode = thefaults.faultcode and
         f.statusid in (2, 4) left outer join
      download_time d
      ON f.downloadtimeid = d.id
WHERE (d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012') AND
      d.bunoid = 166501
GROUP BY d.bunoid, f.downloadtimeid, d.downloadtime, f.faultcode

I admit: I am using SQL Server syntax to create "thefaults".

Improve this answer

2 Comments

If a faultcode has no associated download time, it will be filtered out by the where clause.
One presumes this is what the questioner wants.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.