CODEWITHSUNDEEP
sql
Hayden
Hayden

Reputation: 407

SQL - Running Select Query with Having Clause

here is the query I want to run. 

SELECT COUNT(tableA.ID)
FROM tableA
NATURAL JOIN tableB
NATURAL JOIN tableC
WHERE tableB.Time IS NULL
GROUP BY tableA.ID
HAVING COUNT(tableA.ID) < tableC.Quantity

This query will run perfectly fine without the HAVING clause, however the HAVING clause has an error which I can't pick out.

The purpose of the HAVING clause is that I want to return ID's that have less than the Quantity threshold (which is defined as tableC.Quantity).

How can I fix my current HAVING clause to incorporate that the query only returns ID's that are less than the tableC.Quantity.

Note: if you need more clarification, I can provide more.

Upvotes: 0

Views: 96

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

I am going to assume that the error is something to the effect that tableC.quantity is not in the group by clause (and that you are not using MySQL). If so, you can fix this by using an aggregation function:

SELECT COUNT(tableA.ID)
FROM tableA NATURAL JOIN
     tableB NATURAL JOIN
     tableC
WHERE tableB.Time IS NULL
GROUP BY tableA.ID
HAVING COUNT(tableA.ID) < max(tableC.Quantity);

By the way, I think natural join is a dangerous operation. You could add a new column to a table and invalidate all your queries, with no error message to tell you what is going wrong.

Upvotes: 2

Related Questions