CODEWITHSUNDEEP
sql
SwiftedMind
SwiftedMind

Reputation: 4287

SQL - Select by condition based on multiple rows

I have these two tables:

actions 
action_data

action_data belongs to actions and has the columns: action_id, name, value

The contents may look like this:

Actions:

id | 
-----
178| 
179|

action_data:

action_id |   name   | value
-------------------------------------
178       |  planet  | earth
178       |  object  | spaceship_a
179       |  planet  | earth
179       |  object  | building

Now I want to select the action, which has planet = earth and object = spaceship_a in action_data.

How can I achieve this with SQL? If you had only one condition it would work like this:

SELECT DISTINCT 
    actions.* 
FROM 
    actions 
INNER JOIN 
    action_data ON actions.id = action_data.action_id 
WHERE 
    (action_data.name = 'planet' AND action_data.value = 'earth');

But I need two or more conditions from action_data.

Any ideas?

Upvotes: 2

Views: 2466

Answers (5)

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81137

Since you don't know the number of meta data to search for, I wouldn't recommend unknown/unlimited number of joins.

Instead use group concatenation:

select * from actions 
  join (
    select action_id,
      group_concat(name,'=',value order by name separator ',') as csv // MySQL
//    string_agg(name || '=' || value, ',' order by name) as csv // PostgreSQL
    from meta 
    where name in ('planet', 'object') 
    group by action_id
  ) meta 
  on actions.id = meta.action_id 
  where csv = 'object=building,planet=earth'

I'm happy to hear SQL pros about performance, which, I suppose, would be better in case of 3+ values to find.

Upvotes: 1

binoternary
binoternary

Reputation: 1915

A few more options:

1) Using exists

select *
from actions a
where exists (select 1 from action_data ad 
    where ad.action_id = a.id and ad.name = 'planet' and ad.value = 'earth')
and exists (select 1 from action_data ad 
    where ad.action_id = a.id and ad.name = 'object' and ad.value = 'spaceship_a');

2) using with

with q1 as (
    select action_id
    from action_data
    where name = 'planet' and value = 'earth'
),
q2 as (
    select action_id
    from action_data
    where name = 'object' and value = 'spaceship_a'
)
select *
from q1 inner join q2 on q1.action_id = q2.action_id;

Upvotes: 0

felixgaal
felixgaal

Reputation: 2423

If you don't want a DBMS-specific syntax, you could use an auto-join.

I would do it like this:

SELECT DISTINCT action_id
FROM action_data a1 JOIN action_data a2 USING(action_id)
WHERE 
    a1.name = 'planet' AND a1.value = 'earth' AND
    a2.name = 'object' AND a2.value = 'spaceship_a';

This works for 2 conditions, but can be extended to 3 or more with more replicas of the data table in the FROM clause and the corresponding comparision conditions.

In this case, the a1 replica is used for the first condition (planet - earth) and the a2 replica is used for the second condition (object - spaceship_a).

The JOIN allows us to search for the match in all the possible combinations (N rows gives N^2 combinations).

This is probably not the best and most efficient way of doing, but is reliable and is not platform-dependent.

Demo follows:

mysql> select * from action_data;
+-----------+--------+-------------+
| action_id | name   | value       |
+-----------+--------+-------------+
|       178 | planet | earth       |
|       178 | object | spaceship_a |
|       179 | planet | earth       |
|       179 | object | building    |
+-----------+--------+-------------+
4 rows in set (0.02 sec)

mysql> SELECT DISTINCT action_id
    -> FROM action_data a1 JOIN action_data a2 USING (action_id)
    -> WHERE 
    ->     a1.name = 'planet' AND a1.value = 'earth' AND
    ->     a2.name = 'object' AND a2.value = 'spaceship_a';
+-----------+
| action_id |
+-----------+
|       178 |
+-----------+
1 row in set (0.00 sec)

Upvotes: 3

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

If there are constant number of conditions, you can use join, which will be much more faster then grouping with sums and cases.

If there are 2 conditions, you can join like this:

    declare @t TABLE(id int, name NVARCHAR(MAX), value NVARCHAR(MAX))

    INSERT INTO @t VALUES(1, 'planet', 'earth')
    INSERT INTO @t VALUES(1, 'object', 'spaceship_a')
    INSERT INTO @t VALUES(1, 'destination', 'mars')

    SELECT * FROM @t t1
    JOIN @t t2 ON t1.ID = t2.id
    WHERE t1.name = 'planet' AND t1.value = 'earth' 
    AND t2.name = 'object' AND t2.value = 'spaceship_a'

Of course, if you have 3 conditions, then you will need to join 2 times and add new filters:

    SELECT * FROM @t t1
    JOIN @t t2 ON t1.ID = t2.id
    JOIN @t t3 ON t1.ID = t3.id
    WHERE t1.name = 'planet' AND t1.value = 'earth' 
    AND t2.name = 'object' AND t2.value = 'spaceship_a'
    AND t3.name = 'destination' AND t3.value = 'mars'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I approach these using problems using group by and having, because this is a very general approach that works for many conditions.

In your case:

select ad.action_id
from action_data ad
group by ad.action_id
having sum(case when name = 'planet' and value = 'earth' then 1 else 0 end) > 0 and
       sum(case when name = 'object' and value = 'spaceship_a' then 1 else 0 end) > 0;

Each condition in the having clause counts the number of rows that match. The > 0 means there is at least one.

You can join back to the actions table to get more columns if you want them.

Upvotes: 0

Related Questions