Returning a specific list of identifiers in all subsets
I have two tables
Table "public.tags_to_entities"
Column | Type | Modifiers
--------+---------+-----------
tid | integer | not null
eid | integer | not null
Table "public.tag_tree"
Column | Type | Modifiers
--------+---------+-----------
tid | integer | not null
pid | integer | not null
level | integer |
tag_tree
contains all relationships between tags, which means it SELECT pid FROM tag_tree WHERE tid = ?
will return the parents of the tags. Column level
is or ORDER BY
.
I want to return a list of everyone eid
that has at least one tag in each subset of tags. Executing one subset works using the following query
SELECT DISTINCT eid
FROM tags_to_entities
WHERE
tags_to_entities.tid = 1 OR
tags_to_entities.tid IN (SELECT tag_tree.tid FROM tag_tree WHERE tag_tree.pid = 1));
This returns all eid
existing in tag 1 or one of its child tags. If I want to return everything eid
existing in at least one of the tags associated with 1
and 2
. So far my unfortunate approach is
SELECT DISTINCT eid
FROM tags_to_entities
WHERE
(
tags_to_entities.tid = 1 OR
tags_to_entities.tid IN (SELECT tag_tree.tid FROM tag_tree WHERE tag_tree.pid = 1)) AND
(
tags_to_entities.tid = 2 OR
tags_to_entities.tid IN (SELECT tag_tree.tid FROM tag_tree WHERE tag_tree.pid = 2));
It won't work as it cannot be both 1 and 2. How can I solve this? (After that eid will be connected to the record table)
source to share
You need to use GROUP BY
instead DISTINCT
. This allows aggregate functions and clause to be used HAVING
.
SELECT
map.eid
FROM
tags_to_entities AS map
INNER JOIN
tag_tree AS tree
ON map.tid = tree.tid
WHERE
(tree.tid = 1 OR tree.pid = 1)
OR (tree.tid = 2 OR tree.pid = 2)
GROUP BY
map.id
HAVING
COUNT(DISTINCT CASE WHEN (tree.tid = 1 OR tree.pid = 1) THEN 1
WHEN (tree.tid = 2 OR tree.pid = 2) THEN 2
END)
= 2
Offer JOIN
and WHERE
get all entities that have the 1
or tag 2
. But then you group them together and then you count how many different categories those tags are. Only if it is 2, then the object receives through the offer HAVING
.
source to share