Is there any query to find this id information?

Question

Her is a sample of my table:

+--------+-------+-----+
|  name  | value | id  |
+--------+-------+-----+
| value1 |     1 | 100 |
| value2 |     2 | 100 |
| value1 |     1 | 200 |
| value2 |     3 | 200 |
| value1 |     1 | 300 |
| value2 |     4 | 300 |
|        |       |     |
+--------+-------+-----+

How to setup an SQL query to retrieve the id value 100 for given value1 = 1 and value2 = 2?

Answer 1

If I understand correctly, aggregation with a having clause does what you want:

select id
from t
group by id
having count(*) filter (where name = 'value1' and value = 1) = 1 and
       count(*) filter (where name = 'value2' and value = 2) = 1 ;

   

Answer 2

Group by id and set the condition in the having clause:

select id
from tablename
where (name, value) in (('value1', 1), ('value2', 2))
group by id
having count(distinct value) = 2 

See the demo.

Answer 3

A case of relational-division. See:

• How to filter SQL results in a has-many-through relation

This query typically more efficient than what has been suggested so far:

SELECT id
FROM  (SELECT id FROM tbl WHERE name = 'value1' AND value = 1) t1
JOIN  (SELECT id FROM tbl WHERE name = 'value2' AND value = 2) t2 USING (id);

Or equivalent (results in identical query plan):

SELECT id
FROM   tbl t1
JOIN   tbl t2 USING (id)
WHERE  t1.name = 'value1' AND t1.value = 1
AND    t2.name = 'value2' AND t2.value = 2;

db<>fiddle here

Not enforcing uniqueness in the query (like other answers do) as I assume one or more constraints in the table already doing so.

Have a "covering" index on (name, value, id). Optimized index variations are possible, depending on your undisclosed table definition.

But first reconsider your relational design if you are in a position to change it. EAV models are typically sub-optimal. See:

• Is there a name for this database schema of key values?

Answer 4

SELECT id from TABLE_NAME WHERE value IN (1, 2);

This is will retrieve the id column from your table when value column is either 1 or 2