Member Avatar for gearedout

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
  • 62 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by verruckt24
Member Avatar for verruckt24

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.

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.