Beginner having difficulty with SQL query

Question

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.

stackoverflow.com/questions/5290050/… -- are you guys on the same course? — araqnid
– araqnid, Commented Mar 13, 2011 at 16:12
@araqnid: lmao @vulcanizer: I'm not a big fan of helping someone with a rating of one - especially when their first question is for hw or a test, which they should be learning on their own. additionally, I try to avoid questions that are asking for improper use of the language or are asking for something less efficient/accurate - the proper way would be to use a subquery or join in this circumstance. — vol7ron
– vol7ron, Commented Mar 13, 2011 at 16:19

Elemental · Accepted Answer · 2011-03-14 09:52:11Z

2

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

edited Mar 14, 2011 at 9:52

answered Mar 13, 2011 at 16:11

Elemental

7,5522 gold badges31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vol7ron Over a year ago

might also want to thrown in a and min(N4)=0 since the table could theoretically be populated with negatives

joelt · Accepted Answer · 2011-03-13 16:10:07Z

1

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

answered Mar 13, 2011 at 16:10

joelt

2,6902 gold badges25 silver badges33 bronze badges

Comments

dreyercito · Accepted Answer · 2011-03-13 16:12:54Z

0

You could also use two queries and the minus operator.

answered Mar 13, 2011 at 16:12

dreyercito

642 bronze badges

Comments

Pete Hamilton · Accepted Answer · 2011-03-15 19:59:38Z

0

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

answered Mar 15, 2011 at 19:59

Pete Hamilton

7,9607 gold badges36 silver badges59 bronze badges

Collectives™ on Stack Overflow

Beginner having difficulty with SQL query

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related