Group by having count less than n or null should return true-CodePudding

I want this query to return 1 also in the case when no record exists. With a count less than 2 or even if no records exist it should return 1.

SELECT 1
FROM dbo.Product BS                       
WHERE BS.ASIN = '072142452X'
GROUP BY ASIN 
HAVING COUNT(1) < 2

The result should be 1 in the case when no record exists in the table.

List of All Products In table

Product

Id              ASIN                
1               12A
2               12B
3               1AC
4               123
5               12D

List of Accounts

Account
Id              Name
1               A
2               B
3               C

Now Listing happens in various accounts

List of products Listed in various accounts (Product Id and Account combination is unique. Means one product will be listed in one account only once)

ProductListing
Id              ProductId               AccountId
1               2                       1
2               1                       1
3               4                       1
4               2                       2
5               4                       2
6               5                       3

I want if a product is already listed in one account or in two different accounts it should not come in my query of listing product

Query of Listing product in Account 3. So products which are already listed in 3 and in more than 2 accounts should not come

Select P.Id, P.ASIN
FROM dbo.Product P
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.ProductListing PL
    WHERE P.Id = PL.ProductId AND AccountId = 3
) AND EXISTS (
    SELECT 1
    FROM dbo.ProductListing PL                         
    WHERE P.Id = PL.ProductId 
    GROUP BY PL.ProductId 
    HAVING COUNT(1) < 2
)

CodePudding user response：

You can use a CASE expression on the number of rows returned as follows.

DECLARE @Asin varchar(10) = '072142452X';

SELECT
    CASE WHEN (
        SELECT COUNT(*)
        FROM dbo.Product BS                          
        WHERE BS.[ASIN] = @Asin
    ) < 2 THEN 1 ELSE 0 END;

Note: there is no reason to GROUP BY when you are already filtering down to 1 ASIN.

Based on your edit you can modify your query to use the same sub-query as I have already show as follows:

SELECT P.Id, P.[ASIN]
FROM dbo.Product P
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.ProductListing PL
    WHERE PL.ProductId = P.Id AND AccountId = 3
) AND (
    SELECT COUNT(*)
    FROM dbo.ProductListing PL
    WHERE PL.ProductId = P.Id
) < 2;

CodePudding user response：

Drop your GROUP BY line.

Consider how this will always give a row:

SELECT COUNT(1)
FROM dbo.Product BS                          
WHERE  BS.ASIN = '072142452X'

But GROUP BY means you return a row per group, and you don't have any groups because you're filtering out the rows that would've made one.

So this is what you want.

SELECT 1 
FROM dbo.Product BS                          
WHERE  BS.ASIN = '072142452X'
Having Count(1) < 2

ADDENDUM: I wrote it this way because it's the closest to the OP's original form. But another option, which doesn't get caught up in the nuances of GROUP BY and HAVING is:

SELECT 1 
WHERE (
        SELECT COUNT(1) 
        FROM dbo.Product BS 
        WHERE BS.[ASIN] = '072142452X'
      ) < 2