1,105,334 Community Members

Excluding result from SELECT statement

Member Avatar
blivori
Newbie Poster
20 posts since Aug 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi,

I have a table called BookTitle that holds book information. I have an SQL statement that lists all books that have the same value of a book called 'Northern Lights'. The code works but what I want to do is to exclude 'Northern Lights' from the result.

This is the code I have:

SELECT bt2.btName
FROM BookTitle bt1, BookTitle bt2
WHERE bt1.btName = 'Northern Lights'
AND bt2.value = bt1.value ;

Any help would be appreciated.

Thanks

Member Avatar
simplypixie
Practically a Master Poster
673 posts since Oct 2010
Reputation Points: 123 [?]
Q&As Helped to Solve: 122 [?]
Skill Endorsements: 6 [?]
 
0
 

Use != for 'Not equal to'.
Change this

WHERE bt1.btName = 'Northern Lights'

to this

WHERE bt1.btName != 'Northern Lights'
Member Avatar
hfx642
Posting Pro
515 posts since Nov 2009
Reputation Points: 164 [?]
Q&As Helped to Solve: 105 [?]
Skill Endorsements: 1 [?]
 
0
 

I think what you really want is...

SELECT bt2.btName
FROM BookTitle bt2
WHERE bt2.btName != 'Northern Lights'
AND bt2.value =
(Select bt1.value
 From BootTitle bt1
 Where bt1.btName = 'Northern Lights')
Order by Upper (bt2.btName)
;

This should get you what I think you are looking for.

Member Avatar
simplypixie
Practically a Master Poster
673 posts since Oct 2010
Reputation Points: 123 [?]
Q&As Helped to Solve: 122 [?]
Skill Endorsements: 6 [?]
 
0
 

How do you know that is what they really want and have therefore completely over-complicated the code the OP posted?

Member Avatar
hfx642
Posting Pro
515 posts since Nov 2009
Reputation Points: 164 [?]
Q&As Helped to Solve: 105 [?]
Skill Endorsements: 1 [?]
 
0
 

Well... I don't know about you, but... I read the requirements!

Member Avatar
simplypixie
Practically a Master Poster
673 posts since Oct 2010
Reputation Points: 123 [?]
Q&As Helped to Solve: 122 [?]
Skill Endorsements: 6 [?]
 
0
 

Yes, so did I

The code works but what I want to do is to exclude 'Northern Lights' from the result

Quite simple in their requirements I think

Member Avatar
hfx642
Posting Pro
515 posts since Nov 2009
Reputation Points: 164 [?]
Q&As Helped to Solve: 105 [?]
Skill Endorsements: 1 [?]
 
0
 

Yes... I think the requirements are quite simple.
However... Your modification will return ALL books (even 'Northern Lights'), MULTIPLE times (because of the join to itself).
This does NOT fulfill the requirements the OP posted.

Member Avatar
easy2teach
Newbie Poster
1 post since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Just you have to replace

WHERE bt1.btName != 'Northern Lights'

instead of

WHERE bt1.btName = 'Northern Lights'

Member Avatar
hfx642
Posting Pro
515 posts since Nov 2009
Reputation Points: 164 [?]
Q&As Helped to Solve: 105 [?]
Skill Endorsements: 1 [?]
 
0
 

Your answer is the same as simplypixie's answer.
Sorry... But, this will NOT work.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: