LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 01-20-2004, 09:46 AM   #1
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,378

Rep: Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963
Bizarre SQL select query


Here's something that's been bugging me with SQL recently. Is it possible to create a single query that that can create a list of one non-unique value which *never* has a certain corresponding value in another field? ok.. doesn't make sense... take this table:

Code:
A     B
1     no
1     no
1     yes
2     no
2     no
3     no
4     yes
and to derive the results
Code:
A
2
3
i.e a list of A which have NOT ever had a "yes" next to them. I know this is pretty easy to find using a subquery (i.e. find all the ones that HAVE had a yes and subtract that from a full list) and the equivalent join too, however I'm wondering if there's a way to do this with just ONE query, possibly using a small inline function to make any "yes"'s be selected as the result of an aggregate of some sort, and then lose those from a HAVING or such.....
 
Old 01-20-2004, 09:48 AM   #2
jtshaw
Senior Member
 
Registered: Nov 2000
Location: Seattle, WA USA
Distribution: Ubuntu @ Home, RHEL @ Work
Posts: 3,892
Blog Entries: 1

Rep: Reputation: 66
Give me 2 hours, I think I might know how to do it but before posting something that makes me look like an idiot I am going to finish up the code I am writing and test my solution
 
Old 01-20-2004, 10:07 AM   #3
eantoranz
Senior Member
 
Registered: Apr 2003
Location: Colombia
Distribution: Kubuntu, Debian, Knoppix
Posts: 1,982
Blog Entries: 1

Rep: Reputation: 83
select distinct A from table where B='no' and A not in (select distinct A from table where B='yes')

That should be it, but using 'in' can be a little slow.... but I guess if you can make an OUTER JOIN, you could ask it to select A NOT MATCHING As NOT in the other select (A where B='yes').
 
Old 01-20-2004, 11:36 AM   #4
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,378

Original Poster
Rep: Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963
thanks eantoranz, but that's exactly what i don't want.... you are using a subquery, which i am trying to avoid.
 
Old 01-20-2004, 11:46 AM   #5
jim mcnamara
Member
 
Registered: May 2002
Posts: 964

Rep: Reputation: 34
In Oracle the fastest solution is to use minus:

SELECT fld1 from table1
minus
SELECT fld1 from table1 where
fld2='YES';

This executes fast - the reason is that you MUST do a full-table scan to complete the query anyway. So both 'selects' actually use data from a single full table scan. Sub-queries are not necessarily evil. In this instance (using Oracle) the performance will be the same whether you use the above query or something more complex. You may even degrade performance by using something else.
 
Old 01-20-2004, 12:21 PM   #6
eantoranz
Senior Member
 
Registered: Apr 2003
Location: Colombia
Distribution: Kubuntu, Debian, Knoppix
Posts: 1,982
Blog Entries: 1

Rep: Reputation: 83
How about the outer join?
 
Old 01-20-2004, 12:47 PM   #7
david_ross
Moderator
 
Registered: Mar 2003
Location: Scotland
Distribution: Slackware, RedHat, Debian
Posts: 12,047

Rep: Reputation: 64
I think you need to use a second query such as:
http://www.mysql.com/doc/en/EXISTS_a...ubqueries.html
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL query performance smaida Programming 6 06-08-2005 09:22 AM
Sql + Oracle Select If ???? smaida Programming 11 12-15-2004 08:13 PM
SQL query help pls. vickr1z Programming 8 10-18-2004 11:25 PM
Massive SQL Query patpawlowski Programming 7 03-05-2004 04:24 PM
SQL Query question oulevon Programming 7 01-16-2004 01:50 AM


All times are GMT -5. The time now is 03:25 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration