-2

I have sql server query and i want to get count=0 if record not exist. I want to show all from category table , show all categories with count 0 if not exist. But its return empty if record not exist in where IN clause.

  SELECT TC.DESCRIPTION,count(TE.CategoryID) AS COUNT 
  FROM tblEvent TE
  right JOIN tblCategory TC on TE.CategoryID=TC.NO
  WHERE TE.AssetID IN (
  SELECT ASSET_NO FROM tblAsset WHERE EQUIPMENT_ID=3)
  GROUP BY  TE.CategoryID,TC.DESCRIPTION 

I want to show all categories from category table also with count 0. I am sorry, i cannot explain more, hope understand.I also try all the joins left,right,left out,right outer but its show empty but when record exist in WHERE IN caluse then its show data otherwise not showing count 0 according to category table.

4
  • 1
    Word problems are hard, and reverse engineering "what I tried" is even harder. Please provide sample data and desired results. It seems like you should be using an outer join and not WHERE IN () but that can only be a guess at this point. Jun 27, 2019 at 13:30
  • Hi @AaronBertrand thanks for response, i already tell you,, this query return nothing this is output of this query,, i want to show all the categories from tblcategory with count zero if record not exist in tblevent but it will show each with zero not show empty. Jun 27, 2019 at 13:34
  • 3
    Help me write this query in SQL Jun 27, 2019 at 13:36
  • Yes I read the question, but without the additional data I asked for, it's still a big jumble of words. Jun 27, 2019 at 13:49

2 Answers 2

3

Hameed,

I believe the challenge you have is you used the IN() command as part of the WHERE when the filter applied to the "conditional table".

(If we re-write the query using a LEFT OUTER JOIN and some slight reformatting just to help me see it better.)

SELECT TC.DESCRIPTION, count(TE.CategoryID) AS COUNT 
FROM tblCategory TC 
    LEFT OUTER JOIN tblEvent TE
        on TE.CategoryID=TC.NO
WHERE TE.AssetID IN (
                    SELECT ASSET_NO 
                    FROM tblAsset 
                    WHERE EQUIPMENT_ID=3
                    )
GROUP BY  TE.CategoryID,TC.DESCRIPTION 

What you are first asking the query engine to do is:

  1. Get All tblCategory records
  2. Find all related tblEvent records where tblEvent.CategoryID = tblCategory.NO

This ends up creating something that could look like this (I may have data types wrong for what is actually in your table but I just wanted to get a concept communicated):

+-------------------------+---------------------+------------------+
| tblCategory.Description | tblEvent.CategoryID | tblEvent.AssetID |
+-------------------------+---------------------+------------------+
| Test                    | 1                   | 1                |
| Test 1                  | 1                   | 1                |
| Other                   | 2                   | 1                |
| Other 1                 | 3                   | 1                |
| A                       | 3                   | 2                |
| B                       | 3                   | 2                |
| C                       | 4                   | 2                |
| D                       | 4                   | 2                |
| E                       | NULL                | NULL             |
| F                       | NULL                | NULL             |
| G                       | 6                   | 3                |
| H                       | 6                   | 3                |
| I                       | 6                   | 4                |
| J                       | 6                   | 5                |
+-------------------------+---------------------+------------------+

When the WHERE condition is applied, this entire result set is then filtered to only show the records which have the appropriate tblEvent.AssetID. For the sake of the example lets say that SELECT ASSET_NO FROM tblAsset WHERE EQUIPMENT_ID=3 only returns the value of '1' and '2'. What is then left over (before applying the GROUP BY and then COUNT pieces) is:

+-------------------------+---------------------+------------------+
| tblCategory.Description | tblEvent.CategoryID | tblEvent.AssetID |
+-------------------------+---------------------+------------------+
| Test                    | 1                   | 1                |
| Test 1                  | 1                   | 1                |
| Other                   | 2                   | 1                |
| Other 1                 | 3                   | 1                |
| A                       | 3                   | 2                |
| B                       | 3                   | 2                |
| C                       | 4                   | 2                |
| D                       | 4                   | 2                |
+-------------------------+---------------------+------------------+

If you slightly modify the query and put this WHERE condition as part of the JOIN your result set will be different but you won't loose the tblCategory records. This happens because all the tblCategory records stay in tact (because we are LEFT OUTER JOIN'ing (which is the same thing as just LEFT JOIN, I just like using the OUTER word as well)) but, not only does tblEvent.CategoryID need to equal tblEvent.NO. But tblEvent.AssetID must also match the result of SELECT ASSET_NO FROM tblAsset WHERE EQUIPMENT_ID=3 in order to come back to the main SELECT.

SELECT TC.DESCRIPTION, count(TE.CategoryID) AS COUNT 
FROM tblCategory TC 
    LEFT OUTER JOIN tblEvent TE
        on TE.CategoryID=TC.NO
        AND TE.AssetID IN (
                            SELECT ASSET_NO 
                            FROM tblAsset 
                            WHERE EQUIPMENT_ID=3
                            )
GROUP BY  TE.CategoryID,TC.DESCRIPTION 

This should return the below table right before running your GROUP BY and COUNT pieces (again assuming SELECT ASSET_NO FROM tblAsset WHERE EQUIPMENT_ID=3 only returns the value of '1' and '2'):

+-------------------------+---------------------+------------------+
| tblCategory.Description | tblEvent.CategoryID | tblEvent.AssetID |
+-------------------------+---------------------+------------------+
| Test                    | 1                   | 1                |
| Test 1                  | 1                   | 1                |
| Other                   | 2                   | 1                |
| Other 1                 | 3                   | 1                |
| A                       | 3                   | 2                |
| B                       | 3                   | 2                |
| C                       | 4                   | 2                |
| D                       | 4                   | 2                |
| E                       | NULL                | NULL             |
| F                       | NULL                | NULL             |
| G                       | NULL                | NULL             |
| H                       | NULL                | NULL             |
| I                       | NULL                | NULL             |
| J                       | NULL                | NULL             |
+-------------------------+---------------------+------------------+

This result set should return the values you are looking for, or at least get you a lot closer.

Does this give you the guidance you are looking for?

2
  • 1
    Thankssssssssssssssssssssssssssssssssss alot bro ! Jun 27, 2019 at 16:03
  • @HameedKhan no problem. I remember when this difference was something I learned. This specific difference is something I use all the time. I hope it helps you for the rest of your career! Jun 27, 2019 at 16:07
-2

THE CODE THAT WILL GET THE DESIRED OUTPUT IS :

SELECT
TC.DESCRIPTION,
SUM(CASE WHEN TE.CategoryID IS NULL THEN 0 ELSE 1 END) COUNT
FROM tblCATEGORY TC LEFT JOIN
(tblEVENT TE INNER JOIN tblAsset TA ON TE.AssetID = TA.ASSET_NO AND TA.EQUIPMENT_ID = 3) ON TC.NO = TE.CategoryID
GROUP BY TC.DESCRIPTION;

1
  • This would be better with some formatting, an explanation of how it works, and how it differs from the existing answer. Feb 3, 2020 at 9:55

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.