I have created a select statement that brings together data from three database tables. Here's the source data from the three tables:

Table 1:

Table 2:

Table 3:

Here is the SQL query:

SELECT name as "Group name", limit as "Membership limit", 
(SELECT count(userid) FROM Table3 WHERE Table3.groupid = Table1.id) as "Current number of members" 
FROM Table1 
INNER JOIN Table2 
ON Table2.groupid = Table1.id 
ORDER BY name

The above query correctly brings up the data as follows:

SQL Query Results Table

However, I want to filter the results of this query so that the table only shows the rows where the "Current number of members" is greater than the "Membership limit". In this case, it should only show the group named 'Group 3' which has 3 current members when the membership limit is only 2.

I tried including a GROUP BY statement together with a HAVING statement, as follows:

SELECT name as "Group name", limit as "Membership limit", 
(SELECT count(userid) FROM Table3 WHERE Table3.groupid = Table1.id) as "Current group members"
FROM Table1
INNER JOIN Table2
ON Table2.groupid = Table1.id
GROUP BY Table1.id, Table2.limit
HAVING "Current group members" > "Membership limit"
ORDER BY name

This does not bring up any errors when I run the query, but it is returning no data (instead of returning the values for Group 3).

I'm sure I'm doing something wrong that will be obvious to other people and I would appreciate your expert advice!

CodePudding user response：

You can modify the HAVING clause to reference the correct alias in the SELECT statement, like this:

SELECT name as "Group name", limit as "Membership limit",
(SELECT count(userid) FROM Table3 WHERE Table3.groupid = Table1.id) as "Current number of members"
FROM Table1
INNER JOIN Table2
ON Table2.groupid = Table1.id
GROUP BY Table1.id, Table2.limit
HAVING "Current number of members" > limit
ORDER BY name

The issue was that you referenced "Current group members" in the HAVING clause, which doesn't match the alias "Current number of members" used in the SELECT statement.

CodePudding user response：

Wrap your query up in a derived table (the subquery). Then check the number of users it returns:

select "Group name", "Membership limit", "Current group members"
from
(
    SELECT name as "Group name", limit as "Membership limit",
           (SELECT count(userid) FROM Table3
            WHERE Table3.groupid = Table1.id) as "Current group members"
    FROM Table1
    INNER JOIN Table2
      ON Table2.groupid = Table1.id
) dt
where "Current group members" > "Membership limit"