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

Postgresql – Select object that has multiple relations

Example tables would be:

object

id name
0 obj0
1 obj1
2 obj2

attr

id description
0 attr0
1 attr1
2 attr2

link

o_id a_id
0 0
0 1
0 2
1 1

I now want to generate a SQL that returns only objects having at least a given set of attributes. I tried JOIN, but that returns either nothing or too much.

My attempt to get an object with attributes "attr0" and "attr1" (which should match only obj0 in this case):

SELECT o.name
FROM link l
INNER JOIN object o ON o.id = l.o_id
INNER JOIN attr a ON a.id = l.a_id
WHERE a.description = 'attr0'
  AND a.description = 'attr1'

This returns nothing because after the JOIN those are unique lines. With OR instead of AND, it returns both obj0 and obj1.
The result I want to see would be only obj0, because it’s the only one having attr0 and attr1.

Example (wanted) result:
|o.id|o.name|
|:–:|:—-:|
| 0| obj0|

Is there a way to do this inside SQL, instead of having to filter in code later? I feel doing it via SQL and thus in the database would make my code much faster.

Tags:

4

Answers


  1. 0

    A simple approach would be to limit the select on o.id = 0

    SELECT 
    o.name AS object_name,
    a.description AS attribute_description
FROM 
    object o
JOIN 
    link l ON o.id = l.o_id
JOIN 
    attr a ON l.a_id = a.id
WHERE 
    o.id = 0;
    Login or Signup to reply.
  2. 0

    Your query is not going to work due to the "AND". There is no register that satisfies both conditions. Try to change it for an "OR"

    SELECT o.name
FROM link l
INNER JOIN object o ON o.id = l.o_id
INNER JOIN attr a ON a.id = l.a_id
WHERE a.description = 'attr1'
OR a.description = 'attr2'

    Or better, you can use IN

    SELECT o.name
FROM link l
INNER JOIN object o ON o.id = l.o_id
INNER JOIN attr a ON a.id = l.a_id
WHERE a.description IN ('attr1','attr2');

    Then you can filter for the object you want:

    SELECT o.name
FROM link l
INNER JOIN object o ON o.id = l.o_id
INNER JOIN attr a ON a.id = l.a_id
WHERE a.description IN ('attr1','attr2') AND o_id=0;
    Login or Signup to reply.
  3. 0

    Think you need something like this – a simple aggregation grouped by object and filtered with Having clause:

    Select     o.id, o.name
From       object o
Inner join link l ON( l.o_id = o.id )
Group By   o.id,  o.name
Having     Count(Distinct l.a_id) = (Select Count(id) From attr)

    See the fiddle here.

    Login or Signup to reply.
  4. 0

    Find an object that has at least all given attributes

    Your example is messed up, as you look for ‘attr1’ and ‘attr2’ and want obj1 returned which only has ‘attr1’.

    However, you want to select an object, so select from the object table. You want to limit the result, so use a where clause. There is no need to join. Use EXISTS or IN to check whether the desired attributes exist for the object:

    select *
from object
where id in (select o_id from link where a_id = (select id from attr where description = 'attr1'))
  and id in (select o_id from link where a_id = (select id from attr where description = 'attr2'));

    The same with EXISTS:

    select *
from object o
where exists (select null from link l where l.o_id = o.id and l.a_id = (select id from attr where description = 'attr1'))
  and exists (select null from link l where l.o_id = o.id and l.a_id = (select id from attr where description = 'attr2'));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search