CODEWITHSUNDEEP
mysqlsql
VDanyliuk
VDanyliuk

Reputation: 1049

Select by join table values

I have joined some tables such that I have result table like this

<table border="1" style="border-collapse: collapse">
    <tr><th>ID</th><th>name</th><th>val</th></tr>
    
    <tr><td>1</td><td>n1</td><td>false</td></tr>
    <tr><td>1</td><td>n2</td><td>true</td></tr>
    <tr><td>1</td><td>n3</td><td>true</td></tr>

    <tr><td>2</td><td>n1</td><td>true</td></tr>
    <tr><td>2</td><td>n2</td><td>true</td></tr>
    <tr><td>2</td><td>n3</td><td>true</td></tr>

    <tr><td>3</td><td>n1</td><td>false</td></tr>
    <tr><td>3</td><td>n2</td><td>true</td></tr>
    <tr><td>3</td><td>n3</td><td>true</td></tr>
</table>

I need to get ID that for this ID n1=true and n2=true, so from this table only ID 2 will be correct. How to make such requests in general for not predefined name and value?

Upvotes: 0

Views: 26

Answers (2)

Stefano Zanini
Stefano Zanini

Reputation: 5916

A possible solution would be

select  distinct id
from    yourTable t1
join    yourTable t2
on      t1.id = t2.id
where   t1.name = n1 and t1.val = 'true' and
        t2.name = n2 and t2.val = 'true';

Upvotes: 1

Mihai
Mihai

Reputation: 26784

SELECT id FROM t
GROUP BY id
HAVING SUM(name='n1' AND val='true')>0
AND SUM(name='n2' AND val='true')>0

Upvotes: 1

Related Questions