CODEWITHSUNDEEP
sqlselectmariadb
Captain Trojan
Captain Trojan

Reputation: 2921

SQL - select row only if dual row exists

Syntax: MariaDB

Table structure:

id [INT], value [INT], flag [INT]; id + flag is a primary key (no duplicate id + flag pair)

Sample data:

[id] | [value] | [flag]
111  | 16      | 0               # row 111, 18, 1 exists (3rd)
222  | 17      | 1               # row 222, 20, 0 exists (5th)
111  | 18      | 1               # row 111, 16, 0 exists (1st)
333  | 19      | 1               # no row 333, ??, 0 exists
222  | 20      | 0               # row 222, 17, 1 exists (2nd)
444  | 21      | 0               # no row 444, ??, 1 exists

I need to select value pairs corresponding to flag=0 and flag=1 respectively, but only if both rows exist in the table. Expected result after a magical SELECT statement:

[value_0] | [value_1]
16        | 18
20        | 17

What I've tried:

SELECT id, value, flag FROM table as p WHERE COUNT(SELECT id from table where id = p.id) = 2

Couldn't think of anything else. My SQL knowledge is very basic. If you cannot help me, please at least show me how I could SELECT something like this

[value_0] | [value_1]
NULL      | 19
16        | 18
20        | 17
21        | NULL,

as I assume this is easier. This is exactly the same as the expected output, except that this selected table also contains rows containing exactly one NULL value (the corresponding flag is missing in the original table).

Thank you.

Upvotes: 1

Views: 498

Answers (3)

meer
meer

Reputation: 132

@forpas answer is correct but if you also need null values you can do some thing like this

select * from
(select id,value,flag from tablename where flag = 0) as val0
LEFT JOIN (select id,value,flag from tablename where flag = 1) as val1 on val0.id = val1.id
UNION 
select * from
(select id,value,flag from tablename where flag = 0) as val0
 RIGHT JOIN (select id,value,flag from tablename where flag = 1) as val1 on val0.id = val1.id

See Demo Here Demo

Upvotes: 0

forpas
forpas

Reputation: 164089

It is very simple with a self join:

select t1.value value_0, t2.value value_1
from tablename t1 inner join tablename t2
on t1.id = t2.id and t1.flag = 0 and t2.flag = 1

See the demo.
Results:

> value_0 | value_1
> ------: | ------:
>      20 |      17
>      16 |      18

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use aggregation:

select id, min(value), max(value)
from t
group by id
having min(flag) = 0 and max(flag) = 1;

If there can be more than two rows for an id, you might want group_concat():

select id, group_concat(value)
from t
group by id
having min(flag) = 0 and max(flag) = 1;

If you want the values to line up, use conditional aggregation:

select id,
       max(case when flag = 0 then value end) as value_0,
       max(case when flag = 1 then value end) as value_1
from t
group by id
having min(flag) = 0 and max(flag) = 1;

Upvotes: 1

Related Questions