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

Filed under:
|
|
|

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

Related posts about mysql

Related posts about sql