Beginner having difficulty with SQL query
Posted
by
Vulcanizer
on Stack Overflow
See other posts from Stack Overflow
or by Vulcanizer
Published on 2011-03-13T16:03:02Z
Indexed on
2011/03/13
16:10 UTC
Read the original article
Hit count: 155
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.
© Stack Overflow or respective owner