CODEWITHSUNDEEP
mysqlsqldatabase
Vulcanizer
Vulcanizer

Reputation: 1

Beginner having difficulty with SQL query

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.

Upvotes: 0

Views: 174

Answers (4)

Pete Hamilton
Pete Hamilton

Reputation: 7920

Why do Max and Min when you can just take advantage of grouping like joelt said?

SELECT n1,
       n2,
       n3
FROM   data
GROUP  BY n1,
          n2,
          n3
HAVING Max(n4)=0 AND Min(n4)=0;

Would be one option

Upvotes: 0

Elemental
Elemental

Reputation: 7521

Seems to me you could try something with a grouping and having clause like

Select N1,N2,N3 from Data group by N1,N2,N3 having Max(N4)=0 and MIN(N4)=0

Upvotes: 2

dreyercito
dreyercito

Reputation: 64

You could also use two queries and the minus operator.

Upvotes: 0

joelt
joelt

Reputation: 2680

Since this is homework...start by thinking about grouping.

Upvotes: 1

Related Questions