CODEWITHSUNDEEP
sqlpostgresql
Nathan
Nathan

Reputation: 7855

Can someone explain why this query isn't working?

SELECT searches.id, searches.tags, STRING_AGG(tags.name,',') tag_list 
FROM searches
INNER JOIN notes ON notes.id = searches.note_id
INNER JOIN tags ON tags.note_id = notes.id
WHERE searches.tags <> 'tags_list'
GROUP BY searches.id
LIMIT 1;

I'm getting this result

  id   |         tags         |       tag_list
-------+----------------------+----------------------
 40283 | abcde,hello,sed,test | abcde,hello,sed,test

I've specified to return only cases where searches.tags <> 'tags_list' but am seeing the opposite result.

Sample data

SELECT id, tags, note_id FROM searches WHERE searches.note_id = 3;

  id   |            tags             | note_id
-------+-----------------------------+---------
 40285 | at,asperiores,incidunt,unde |       3
(1 row)


SELECT id, name, note_id FROM tags WHERE tags.note_id = 3;

 id |    name    | note_id
----+------------+---------
  6 | at         |       3
  7 | asperiores |       3
  8 | incidunt   |       3
  9 | unde       |       3
(4 rows)

I would expect my query does not return search 40285 when I use a <> operator. Conversely it would return it if I used a = operator.

Upvotes: 0

Views: 69

Answers (2)

user330315
user330315

Reputation:

You need to use the having clause to filter on the result of an aggregation. But you can't use the column alias in the HAVING clause, you need to repeat the expression.

However, the comparison between the aggregated tag names and the stored tag names is unreliable because the order in the aggregation result is undefined. If you always store the tag names sorted alphabetically in searches.tags then you should sort the names in the aggregation as well.

Note that, if searches.id is not a primary or unique key in the table searches, then you would also need to include searches.tags in the group by.

SELECT searches.id, searches.tags, STRING_AGG(tags.name,',' order by tags.name) tag_list 
FROM searches
  JOIN notes ON notes.id = searches.note_id
  JOIN tags ON tags.note_id = notes.id
GROUP BY searches.id
HAVING searches.tags <> STRING_AGG(tags.name,',' order by tags.name)
LIMIT 1

If you don't want to repeat the expression, use a derived table:

select *
from (
  SELECT searches.id, searches.tags, STRING_AGG(tags.name,',' order by tags.name) tag_list 
  FROM searches
    JOIN notes ON notes.id = searches.note_id
    JOIN tags ON tags.note_id = notes.id
  GROUP BY searches.id
) t
WHERE tags <> tag_list
LIMIT 1

Upvotes: 4

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

add searches.tags column in group by as you are using aggregation

SELECT searches.id, searches.tags, STRING_AGG(tags.name,',') tag_list 
FROM searches
INNER JOIN notes ON notes.id = searches.note_id
INNER JOIN tags ON tags.note_id = notes.id
WHERE searches.tags <> 'tags_list'
GROUP BY searches.id,searches.tags
LIMIT 1;

Upvotes: 1

Related Questions