CODEWITHSUNDEEP
sqlsqlite
qwerty
qwerty

Reputation: 546

SELECT multiple times same column returns duplicated

I have the following tables:

table1:

id | idFirst | idLast | value
1  |    1    |   2    |  0
2  |    4    |   5    |  0
3  |    6    |   8    |  1

table1.idFirst and table1.idLast are FK pointing to table2.id

table2:

id | level
1  | 123
2  | 124
3  | 125
4  | 126
5  | 127
6  | 128
7  | 129
8  | 130

I want to perform a query that returns table2.level of all the registers on table1 where table1.value==0. My approach was the following:

SELECT T1.id
(SELECT T2.level WHERE T1.idFirst=T2.id) AS x1,
(SELECT T2.level WHERE T1.idLast=T2.id) AS x2
FROM table1 T1
INNER JOIN table2 T2 ON T1.idFirst=T2.id OR T1.idLast=T2.id
WHERE table1.value=0
GROUP BY T1.id
ORDER BY T1.id ASC;

The result of this query has the following structure:

enter image description here

The problem with this is that the GROUP BY is not correctly grouping the registers x1 and x2, and therefore the result is null for x1 while the column x2 has correct values. If I delete the GROUP BY statement, all the expected registers are obtained but as they are not groupped, as can be seen bellow:

enter image description here

Therefore, my question is: how can I perform this query without losing all the registers of one of the columns?

Upvotes: 1

Views: 43

Answers (1)

forpas
forpas

Reputation: 164089

You must join table1 to 2 copies of table2:

select t1.id, 
       t21.level x1,
       t22.level x2
from table1 t1 
inner join table2 t21 on t21.id = t1.idFirst
inner join table2 t22 on t22.id = t1.idLast
where t1.value = 0;

See the demo.
Results:

| id  | x1  | x2  |
| --- | --- | --- |
| 1   | 123 | 124 |
| 2   | 126 | 127 |

Upvotes: 2

Related Questions