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

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

WHERE bt1.btName = 'Northern Lights'

to this

WHERE bt1.btName != 'Northern Lights'
Member Avatar for hfx642

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.

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

Member Avatar for hfx642

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

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 for hfx642

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.

Just you have to replace

WHERE bt1.btName != 'Northern Lights'

instead of

WHERE bt1.btName = 'Northern Lights'

Member Avatar for hfx642

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.