Question 309901 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I filter data through a join table?

There are tables: groups, targets and join table groups_targets.

Groups have targets, for example:

  • Group #1 has a gender target, an age target, and a city target
  • Group #2 has a gender target
  • Group #3 has a city and gender target.
  • Group #4 has an age and city target.

My request does not return anything

SELECT DISTINCT groups.* 
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id 
WHERE groups.user_id = 1 
AND (targets.name = 'age' AND targets.name = 'gender')

How to find groups that have both gender and age, only group #1.

Tags:

3

Answers


  1. 0

    (targets.name = 'age' AND targets.name = 'gender') will always return false, targets.name can’t have two values at the same time, Use OR instead of AND :

    SELECT DISTINCT groups.* 
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id 
WHERE groups.user_id = 1 
AND (targets.name = 'age' OR targets.name = 'gender');

    If both targets should be there, we can use GROUP BY and HAVING clauses alongside the CASE clause to specifically choose groups containing both targets :

    SELECT groups.id
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id 
WHERE groups.user_id = 1 
GROUP BY groups.id
HAVING COUNT(CASE WHEN targets.name = 'age' THEN 1 END) >= 1
       AND COUNT(CASE WHEN targets.name = 'gender' THEN 1 END) >= 1
    Login or Signup to reply.
  2. 0

    To get all groups having target age use the join query with the proper filter on target name

    SELECT   groups.id 
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id and targets.name = 'age' 
WHERE groups.user_id = 1;

id|
--+
 1|
 4|

    Use similar query for the target gender

    SELECT   groups.id 
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id and targets.name = 'gender' 
WHERE groups.user_id = 1;

id|
--+
 1|
 2|
 3|

    To get groups with both targets use intersect of the previous queries, i.e. group_id‘s returned in both queries

    SELECT   groups.id 
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id and targets.name = 'age' 
WHERE groups.user_id = 1 
intersect
SELECT   groups.id 
FROM groups 
INNER JOIN groups_targets ON groups_targets.group_id = groups.id 
INNER JOIN targets ON targets.id = groups_targets.target_id and targets.name = 'gender' 
WHERE groups.user_id = 1; 

id|
--+
 1|
    Login or Signup to reply.
  3. 0

    To find groups that have both gender and age targets, you should use a different approach, such as using the EXISTS clause with subqueries.

    SELECT DISTINCT groups.* 
FROM groups 
WHERE groups.user_id = 1 
AND EXISTS (
    SELECT 1
    FROM groups_targets 
    INNER JOIN targets ON targets.id = groups_targets.target_id 
    WHERE groups_targets.group_id = groups.id 
    AND targets.name IN ('age', 'gender')
    GROUP BY groups_targets.group_id
    HAVING COUNT(DISTINCT targets.name) = 2
);

    The query should give you the desired result, which is finding groups that have both gender and age targets.

    Hope this helps.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search