Hi, I've been studying SQL for 2 weeks now and I'm preparing for an SQL test. Anyway I'm trying to do this question:
For the table:
1 create table data {
2 id int,
3 n1 int not null,
4 n2 int not null,
5 n3 int not null,
6 n4 int not null,
7 primary key (id)
8 }
I need to return the relation with tuples (n1, n2, n3) where all the corresponding values for n4 are 0. The problem asks me to solve it WITHOUT using subqueries(nested selects/views)
It also gives me an example table and the expected output from my query:
01 insert into data (id, n1, n2, n3, n4)
02 values (1, 2,4,7,0),
03 (2, 2,4,7,0),
04 (3, 3,6,9,8),
05 (4, 1,1,2,1),
06 (5, 1,1,2,0),
07 (6, 1,1,2,0),
08 (7, 5,3,8,0),
09 (8, 5,3,8,0),
10 (9, 5,3,8,0);
expects
(2,4,7)
(5,3,8)
and not (1,1,2) since that has a 1 in n4 in one of the cases.
The best I could come up with was:
1 SELECT DISTINCT n1, n2, n3
2 FROM data a, data b
3 WHERE a.ID <> b.ID
4 AND a.n1 = b.n1
5 AND a.n2 = b.n2
6 AND a.n3 = b.n3
7 AND a.n4 = b.n4
8 AND a.n4 = 0
but I found out that also prints (1,1,2) since in the example (1,1,2,0) happens twice from IDs 5 and 6.
Any suggestions would be really appreciated.
