Geek Answers Handbook

HAVING clause in PostgreSQL

I am rewriting MySQL queries in PostgreSQL. I have a table with articles and another table with categories. I need to select all categories in which there is at least 1 article:

SELECT c.*,(
    SELECT COUNT(*) 
    FROM articles a 
    WHERE a."active"=TRUE AND a."category_id"=c."id") "count_articles" 
FROM articles_categories c 
HAVING (
    SELECT COUNT(*) 
    FROM articles a 
    WHERE a."active"=TRUE AND a."category_id"=c."id" ) > 0

I do not know why, but this request causes an error:

ERROR:  column "c.id" must appear in the GROUP BY clause or be used in an aggregate function at character 8
+5
source share
2 answers

The sentence is a HAVINGlittle hard to understand. I am not sure how MySQL interprets it. But the Postgres documentation can be found here:

http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-HAVING

It basically says:

HAVING , GROUP BY. , , GROUP BY. , SELECT HAVING . , HAVING , , .

, , HAVING GROUP BY SQL: 1999 "grand total", .. GROUP BY ( ) ( PostgreSQL)

, , HAVING .

, JOIN articles_categories articles:

SELECT DISTINCT c.*
FROM articles_categories c
JOIN articles a 
ON a.active = TRUE 
AND a.category_id = c.id

:

SELECT *
FROM articles_categories c
WHERE EXISTS (SELECT 1 
                FROM articles a
               WHERE a.active = TRUE 
                 AND a.category_id = c.id)
+11
select * from categories c
where
exists (select 1 from article a where c.id = a.category_id);

... , ;)

0

More articles:


All Articles