0

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
Lois2B
  • 111
  • 1
  • 16
  • Add some sample table data and the expected result - all as formatted text, not images. – jarlh Nov 26 '18 at 09:51
  • please provide sample data and expected output in table format – Fahmi Nov 26 '18 at 09:52
  • 2
    Try changing `HAVING (SELECT COUNT(DISTINCT CategoryID) FROM Categories) = @v` to `HAVING COUNT(C.CategoryID) = @v`. At the moment your HAVING statement has nothing to do with the data in the select statement. – SazooCat Nov 26 '18 at 09:54
  • I think the root of your question might be answered here https://stackoverflow.com/questions/287474/whats-the-difference-between-having-and-where – Dwight Reynoldson Nov 26 '18 at 09:55
  • @SazooCat thank you too, that solved the problem, but I had to add distinct to the count. – Lois2B Nov 26 '18 at 10:13

1 Answers1

1

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(*).

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • It's not duplicate article, but duplicate category article published. Thank you, COUNT(DISTINCT A.CategoryID) worked! – Lois2B Nov 26 '18 at 10:11