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.
10
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'));
2
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)
Just for the record:
The same result for just the 2 of attrs (if that is the issue) could be done just changing the Having clause into one of the folloving two:
Having Count(Distinct Case When l.a_id IN(0, 1) Then l.a_id End) = (Select Count(id) From attr Where id IN(0, 1))
-- OR
Having Count(Distinct Case When l.a_id !=2 Then l.a_id End) = (Select Count(id) From attr Where id != 2)
See the fiddle here.
3
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;
1
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;
If you want both ATTR:
SELECT o.id, o.name
FROM object o
JOIN link l ON o.id = l.o_id
JOIN attr a ON l.a_id = a.id
WHERE a.description IN ('attr1', 'attr2')
GROUP BY o.id, o.name
HAVING COUNT(DISTINCT a.id) = 2;
2