1

I am stuck with a query for some time since today morning. The query will show all records from table A and count of a column in table B, which includes 0 values if there are no occurrences (This is working fine) But the moment I am joining table C to extract the corresponding description of each record from table B, all the 0 value records are omitted!!

Here is my Query 1 (working fine w/o description field) (see screenshot of result result of Query1)

SELECT aa.awardcode, aa.year_grp, aa.awardqty, COUNT(aw.stud_code) as 
stud_count from awardallocation AS aa
LEFT JOIN awardwinner as aw
ON aa.awardcode = aw.awardcode
AND aa.year_grp = aw.year_grp
AND aw.year = '$year'
AND aa.status = 1
AND aw.awardstatus = 1
GROUP BY aa.awardcode, aa.year_grp

Here is Query 2 (NOT displaying any record with Zero values when Description column is added)

SELECT aa.awardcode, ad.award_description, aa.year_grp, aa.awardqty, 
COUNT(aw.stud_code) from awardallocation AS aa
LEFT JOIN awardwinner as aw
ON aa.awardcode = aw.awardcode
AND aa.year_grp = aw.year_grp
INNER JOIN award as ad
ON aa.awardcode = ad.awardcode
AND aw.year = 2017
AND aa.status = 1
AND aw.awardstatus = 1
GROUP BY aa.awardcode, aa.year_grp

What condition should I add to Query2 to force it to display Zero values?

NB: I am using MySQL. Column: awardcode is a present in all three tables and is unique in Award table. Column: year_grp is present in Awardallocation and Awardwinner table.

Thank you.

Tables

Expected result

Salman A
  • 262,204
  • 82
  • 430
  • 521
guptagee
  • 13
  • 5

1 Answers1

0
  • You need to basically shift your Where conditions on the awardwinner table to the left join ON condition. It is because, there are cases where there is no matching entry found in the awardwinner table and they will be returned as null. But your where condition will filter them out, and that is why you are not getting the cases where there is no awardwinner.
  • Now, with multiple joins on various tables, there is a possibility of duplicate row(s). So, you need to do COUNT (DISTINCT ...) to ensure duplicate counts are not received.
  • Also, I have added all the required columns (non-aggregated ones) in the Select clause to Group By as well. Do read: Error related to only_full_group_by when executing a query in MySql

Do the following query:

SELECT aa.awardcode, 
       ad.award_description, 
       aa.year_grp, 
       aa.awardqty, 
       COUNT(DISTINCT aw.stud_code) AS stud_code_count 
FROM awardallocation AS aa 
INNER JOIN award as ad 
  ON aa.awardcode = ad.awardcode
LEFT JOIN awardwinner as aw 
  ON aa.awardcode = aw.awardcode 
  AND aa.year_grp = aw.year_grp 
  AND aw.year = 2017 
  AND aw.awardstatus = 1
WHERE aa.status = 1 
GROUP BY aa.awardcode, 
         ad.award_description, 
         aa.year_grp, 
         aa.awardqty

Additionally: If you don't want to add these Where conditions to the Join, you can add an OR condition for IS NULL (so that all cases can come).

You can do the following instead: 

SELECT aa.awardcode, 
       ad.award_description, 
       aa.year_grp, 
       aa.awardqty, 
       COUNT(DISTINCT aw.stud_code) AS stud_code_count 
FROM awardallocation AS aa 
INNER JOIN award as ad 
  ON aa.awardcode = ad.awardcode
LEFT JOIN awardwinner as aw 
  ON aa.awardcode = aw.awardcode 
  AND aa.year_grp = aw.year_grp 
WHERE aa.status = 1 
  AND (aw.year = 2017 OR aw.year IS NULL)  
  AND (aw.awardstatus = 1 OR aw.awardstatus IS NULL) 
GROUP BY aa.awardcode, 
         ad.award_description, 
         aa.year_grp, 
         aa.awardqty
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • Hi Madhur, unfortunately its not working. The primary table will be 'awardallocation'. This is because it contains info on which award goes to which Year and how many students will be awarded. There may be a year with Status=0 in 'awardallocation' which will not be considered. Ideally if I use year 2018 it should display all the active records from 'awardallocation' with award_description (from 'award') but with 0 (zero) values stud_count (from 'awardwinner') since no award has been allocated for this year as yet. This is not happening. Your query is not providing that result either. – guptagee Sep 28 '18 at 04:41
  • @guptagee can you create a SQL fiddle or DB fiddle ? – Madhur Bhaiya Sep 28 '18 at 04:59
  • @guptagee no need for SQL Fiddle. Please check the updated answer now. and also, the first step in explanation. Sure to work now, hopefully :-) – Madhur Bhaiya Sep 28 '18 at 05:09
  • @guptagee any updates ? Please check the updated answer for one more possible way. – Madhur Bhaiya Sep 28 '18 at 07:14
  • your revised first answer is working perfectly. Well almost.. there are 223 active (status=1) awardcodes in ‘awardallocation’ table and your query is generating 222 records. I will do some research on which exact record is being ignored and why. Thanks a lot, mate. – guptagee Sep 29 '18 at 10:31
  • @guptagee do let me know what is being ignored. Also, let me know if the second query also works or not! – Madhur Bhaiya Sep 29 '18 at 11:01