0

I'm trying to construct a sql statement to check 2 tables. Here's what I am trying to do.

tableA - is a list of fruits
(fruits)
apples
bananas
pears
grapes
orange

table B - is a list of fruit combinations
(fruit_1) (fruit_2)
apple banana
orange banana
apple pear

I want to get a list of fruits that I have not combined with an apple before. Here's what I have, but can't get the result I want.

SELECT tableA.fruits FROM tableA, tableB WHERE tableA.fruits != apples AND tableB.fruit1 != apples AND tableB.fruit2 != apples;

What I want is
orange
grapes

2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by verruckt24
0

Your query should be of the form :

SELECT a.fruits FROM tableA a where a.fruit NOT IN (SELECT b.fruit2 from tableB b where b.fruit1 = 'apple') AND a.fruits != 'apple';

This will work provided that 'apples' appear only in the fruit1 column of tableB.

Don't expect this query to work out of the box. I won't give you the ready query, thats something that against the rules of this forum. This is just a form of it to show you on what lines you should be thinking over it.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.