CODEWITHSUNDEEP
sqlsql-serverjoingroup-byhaving
Lois2B
Lois2B

Reputation: 121

How to select only users with certain value?

In my database, I want to group by users, and I'm using three tables: Users, Categories and Articles.

DECLARE @v INT
SELECT @v = COUNT(*)
FROM Categories

SELECT U.Username
FROM Users AS U
JOIN Articles as A ON U.UserID = A.AuthorID
JOIN Categories as C ON C.CategoryID = A.CategoryID
GROUP BY U.UserID, U.Username
HAVING (SELECT COUNT(DISTINCT CategoryID) FROM Categories) = @v

What I want to select is only users who have published articles in each category existing.

I store the number of categories existing in variable @v, and use HAVING to define the condition (that is, only users who have published articles in as many different categories as there exist), but it doesn't seem to work since it will select all users who have published articles regardless of category.

I tried putting the condition to WHERE instead of using HAVING (I'm still not sure about the differences between the two), but I got the same result. I tried counting distinct CategoryID's from table Articles instead of table Categories too, but still got the same result.

What am I missing?

Table Users data:

  userid  | username
----------+-----------
        1 | Joe  
        2 | Sally
        3 | Anne

Table Articles data:

articleid | authorID | categoryid
----------+----------+-------------
        1 | 3        | 3
        2 | 2        | 1
        3 | 1        | 2
        4 | 3        | 1
        5 | 3        | 2

Table Categories data:

categoryid| categoryname
----------+--------------
        1 | sports
        2 | events
        3 | news

As you can see, there are three (3) categories, five (5) articles and three (3) authors (users). All three of them have written articles, but only User with UserID 3 has written articles in each category.

My output:

  | username
------------
1 | Anne
2 | Joe
3 | Sally

Expected output:

  | username
------------
1 | Anne

Upvotes: 0

Views: 776

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I suspect the problem is with the HAVING clause, you can use COUNT(*) or COUNT(DISTINCT CategoryID) in HAVING clause & the rest would be same :

SELECT U.Username
FROM Users AS U JOIN 
     Articles as A 
     ON U.UserID = A.AuthorID JOIN 
     Categories as C 
     ON C.CategoryID = A.CategoryID
GROUP BY U.Username
HAVING COUNT(*) = @v;

However, the Categories table is not needed in JOIN. if one user has duplicate category article published then use COUNT(DISTINCT CategoryID) instead of COUNT(*).

Upvotes: 1

Related Questions