SQL - Select by condition based on multiple rows

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)

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'

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.

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;