4 Replies - 606 Views - Last Post: 15 March 2011 - 01:44 AM Rate Topic: -----

#1 vulcanizer  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 12-March 11

Help with SQL Query

Posted 12 March 2011 - 03:09 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Help with SQL Query

#2 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10397
  • View blog
  • Posts: 38,475
  • Joined: 27-December 08

Re: Help with SQL Query

Posted 12 March 2011 - 03:46 PM

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.
Was This Post Helpful? 1
  • +
  • -

#3 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: Help with SQL Query

Posted 13 March 2011 - 06:05 AM

View Postvulcanizer, 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

Was This Post Helpful? 0
  • +
  • -

#4 vulcanizer  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 12-March 11

Re: Help with SQL Query

Posted 13 March 2011 - 07:01 AM

View PostkeakTheGEEK, on 13 March 2011 - 06:05 AM, said:

View Postvulcanizer, 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

Was This Post Helpful? 0
  • +
  • -

#5 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Help with SQL Query

Posted 15 March 2011 - 01:44 AM

it should be like this
SELECT n1,n2,n3    
FROM   DATA a
GROUP BY a.n1,a.n2,a.n3
HAVING SUM(n4)=0


Was This Post Helpful? 1
  • +
  • -

Page 1 of 1