MySQL - Trying to show results for rows that have 0 records...across 3 columns

Question

There's a lot of Q&A out there for how to make MySQL show results for rows that have 0 records, but they all involve 1-2 tables/fields at most.

I'm trying to achieve the same ends, but across 3 fields, and I just can't seem to get it.

Here's what I've hacked together:

SELECT circuit.circuit_name, county.county_name, result.adr_result, count( result.adr_result ) AS num_results
FROM 

(
SELECT cases.case_id, cases.county_id, cases.result_id
FROM cases
WHERE cases.status_id <> "2"
) q1

RIGHT JOIN county ON q1.county_id = county.county_id
RIGHT JOIN circuit ON county.circuit_id = circuit.circuit_id
RIGHT JOIN result ON q1.result_id = result.result_id
GROUP BY adr_result, circuit_name, county_name
ORDER BY circuit_name, county_name, adr_result

What I need to see is a list of ALL circuits in the first column, a list of ALL counties per circuit in the second column, a list of ALL possible adr_result entries for each county (they're the same for every county) in the third column, and then the respective count for the circuit/county/result combination-- even if it is 0. I've tried every combination of left, right and inner join (I know inner is definitely not the solution, but I'm frustrated) and just can't see where I'm going wrong.

Any help would be appreciated!

Could you please elaborate on what output you're looking for? For example, if you have no records in circuit, county or result, what should the output be? As I've pointed out here http://stackoverflow.com/questions/19221630/count-returning-blank-instead-of-0/19221822#19221822 using GROUP BY will cause your query to not return anything, if there are no records in the grouped columns. — Dan, Oct 07 '13 at 12:36
A quick search on SO returns ca. 47,623 results for "left join" as compared with 2,179 results for "right join". I'm not saying it's wrong (because, technically, for every LEFT JOIN there is also a logically identical RIGHT JOIN) but you might want to be aware of that statistic when constructing your queries. — Strawberry, Oct 07 '13 at 12:37
Dan- If no records exist, I should still see an entry for the circuit, county, and adr_result, but it should have a count of 0. If I take the GROUP BY out I only get a single row with no useful information. I'll try something like the workaround in your post. Thanks! Strawberry- Thanks for the pointer; I seem to use right joins a lot more than most :) — Ivan, Oct 07 '13 at 12:53

score 0 · Answer 1 · answered Oct 07 '13 at 12:54

Here is a start. I can't follow your problem statement completely. For instance, what is the purposes of the cases table? None the less, when you say "ALL" records for each of those tables, I interpret it as a Cartesian product - which is implemented through the derived table in the FROM clause (notice the lack of the JOIN in that clause)

SELECT everthingjoin.circuit_name
  , everthingjoin.county_name
  , everthingjoin.adr_result
  , COUNT(result.adr_result) AS num_results
FROM
  (SELECT circuit.circuit_name, county.county_name, result.adr_result,
  FROM circuit
  JOIN county
  JOIN result) AS everthingjoin
LEFT JOIN cases
ON cases.status_id <> "2"
  AND cases.county_id = everthingjoin.county_id
LEFT JOIN circuit 
  ON everthingjoin.circuit_id = circuit.circuit_id
LEFT JOIN result 
  ON cases.result_id = result.result_id
GROUP BY adr_result, circuit_name, county_name
ORDER BY circuit_name, county_name, adr_result

I need to go run to a meeting, but I'll give it a spin in 15-30 minutes and see what comes back. It looks logical, and I hadn't thought about joining anything in the FROM clause. Thanks for taking a shot at it! — Ivan, Oct 07 '13 at 13:02

score 0 · Answer 2 · answered Oct 07 '13 at 13:03

try this, see if it provides some ideas:

SELECT
        circuit.circuit_name
      , county.county_name
      , result.adr_result
      , ISNULL(COUNT(result.*)) AS num_results
      , COUNT(DISTINCT result.adr_result) AS num_distinct_results
FROM cases
LEFT JOIN county
        ON cases.county_id = county.county_id
LEFT JOIN circuit
        ON county.circuit_id = circuit.circuit_id
LEFT JOIN result
        ON cases.result_id = result.result_id
WHERE cases.status_id <> "2"
GROUP BY
        circuit.circuit_name
      , county.county_name
      , result.adr_result
ORDER BY
        circuit_name, county_name, adr_result

ISNULL() is there to see if it helps. COUNT(DISTINCT ... is there to see if it produces a different result and to help highlight if ISNULL() is useful or not. Really it would be best if we had sample data from each table. — Paul Maxwell, Oct 07 '13 at 13:37

MySQL - Trying to show results for rows that have 0 records...across 3 columns

2 Answers2