[vulcanizer]

Hi, I've got the following table:

create table data {
id int,
n1 int not null,
n2 int not null,
n3 int not null,
n4 int not null,
primary key (id)
}

and what I need to do is 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)

e.g. for:

insert into data (id, n1, n2, n3, n4)
values (111, 2,4,7,0),
(222, 2,4,7,0),
(333, 3,6,9,8),
(444, 1,1,2,1),
(555, 1,1,2,0),
(666, 1,1,2,0),
(777, 5,3,8,0),
(888, 5,3,8,0),
(999, 5,3,8,0);

the query should return:
(2,4,7)
(5,3,8)


I've been studying sql for a week now but I'm stuck on this. The best I could come up with was:

SELECT DISTINCT n1, n2, n3
FROM data a, data b
WHERE a.ID <> b.ID
      AND a.n1 = b.n1
      AND a.n2 = b.n2
      AND a.n3 = b.n3
      AND a.n4 = b.n4
      AND a.n4 = 0

but I have nothing to test the query and I get the feeling it's completely wrong.


Any help would really be appreciated!

This post has been edited by macosxnerd101: 12 March 2011 - 03:42 PM
Reason for edit:: Please use code tags

[macosxnerd101]

The SELECT query looks good to me.

With your INSERT queries, you shouldn't be dealing with the primary key. When you define the table, the PK (when it is an int id) should be set as NOT NULL and AUTO_INCREMENT. Just insert the values except the PK, and let the database engine handle setting the PK values.

[keakTheGEEK]

vulcanizer, on 12 March 2011 - 03:09 PM, said:

SELECT DISTINCT n1, n2, n3
FROM data a, data b
WHERE a.ID <> b.ID      
        AND a.n1 = b.n1      
        AND a.n2 = b.n2      
        AND a.n3 = b.n3      
        AND a.n4 = b.n4      
        AND a.n4 = 0

That query doesn't generate the result set that you indicated you are trying to get. That query produces the following result set (given the data set you provided):

n1	n2	n3
1	1	2
2	4	7
5	3	8

Tuples {1,1,2,0} and tuples {2,4,7,0} both equally satisfy the filters that you specify for your query. That is, both appear twice each with a unique ID (satisfying a.ID <> b.ID). Also, n1, n2, n3, n4 are equal. So you end up with that extra record, rather then returning
(2,4,7)
(5,3,8)
as your final result set. Is the data set that you provided the actual data that you are working with for your problem, or one that you made up as an example? Having the actual data will help because there may be more to it than you think. For example the result set could be

SELECT n1, n2, n3
FROM data
WHERE (n1 + n2 = n3 + n4 OR n1 + n2 = n3 - 1)
AND (n1 + n2 > 2)

There could even be some other relation involved that produces the desired result set...

This post has been edited by keakTheGEEK: 13 March 2011 - 06:06 AM

[vulcanizer]

keakTheGEEK, on 13 March 2011 - 06:05 AM, said:

vulcanizer, on 12 March 2011 - 03:09 PM, said:

SELECT DISTINCT n1, n2, n3
FROM data a, data b
WHERE a.ID <> b.ID      
        AND a.n1 = b.n1      
        AND a.n2 = b.n2      
        AND a.n3 = b.n3      
        AND a.n4 = b.n4      
        AND a.n4 = 0

That query doesn't generate the result set that you indicated you are trying to get. That query produces the following result set (given the data set you provided):

n1	n2	n3
1	1	2
2	4	7
5	3	8

Tuples {1,1,2,0} and tuples {2,4,7,0} both equally satisfy the filters that you specify for your query. That is, both appear twice each with a unique ID (satisfying a.ID <> b.ID). Also, n1, n2, n3, n4 are equal. So you end up with that extra record, rather then returning
(2,4,7)
(5,3,8)
as your final result set. Is the data set that you provided the actual data that you are working with for your problem, or one that you made up as an example? Having the actual data will help because there may be more to it than you think. For example the result set could be

SELECT n1, n2, n3
FROM data
WHERE (n1 + n2 = n3 + n4 OR n1 + n2 = n3 - 1)
AND (n1 + n2 > 2)

There could even be some other relation involved that produces the desired result set...

Thanks for replying and generating the output for my query.

This is a question I was given purely to test my sql query knowledge and so there is no database associated with it. It literally gives the table created and then shows the example situation and expected results. So the query basically needs to just return n1,n2,n3 where n4 is 0 in every case. However I can assume there is no relationship between the n1-n4 values.

This post has been edited by vulcanizer: 13 March 2011 - 07:04 AM

[thava]

it should be like this

SELECT n1,n2,n3    
FROM   DATA a
GROUP BY a.n1,a.n2,a.n3
HAVING SUM(n4)=0