1

I have a table hardest

    One                        Two
   apple                      orange
   orange                     grapes 
   banana                     strawberry
   orange                     strawberry
   grapes                     apple

Now you can see that i have listed here 5 pairs. Let me tell you that it is not an ordinary table because in this table fruits' name(apple) are sometime in column one and sometime in column two.

And another table Description
name             color  
apple            red
orange           orange
strawberry       pink
banana           yellow
grapes           black

Suppose iam banana and i don't want to see the description of fruit which are paired with me. Now can somebody tell me how to display the details of a fruit which has pairing with all of the following in mysql but not with banana: strawberry, grapes, apples.

I am telling the way to do it which is not supported in mysql.

Select * from description 
natural join (select one as name where two='apple' union select two as name where one='apple') as t1 
natural join (select one as name where two='grapes' union select two as name where one='grapes') as t2
natural join (select one as name where two='strawberry' union select two as name where one='strawberry') as t3
MINUS
Select * from description 
natural join (select one as name where two='banana' union select two as name where one='banana') as b6
Improve this question
4
  • 2
    i dont understand your question at all..? Commented Apr 27, 2012 at 11:12
  • what are you not understanding? Commented Apr 27, 2012 at 11:19
  • @AnkitGautam: Your recent update to your question adds a requirement that the resultset omit any fruits that are paired with banana, citing MINUS (which MySQL does not support) as the solution in other RDBMS; there are plenty of examples on this site and others explaining how to effect an equivalent operation in MySQL: indeed, searching Google for "MySQL MINUS" brings up plenty of good results. Have you had a go at implementing one of these solutions yourself? Let us know if you encounter any problems. Commented Apr 28, 2012 at 7:11
  • I have tried left join but the syntax got me crazy as in various sites left join is given for tables but i am using left join to join two joins Commented Apr 28, 2012 at 11:48

4 Answers 4

Reset to default
3 
SELECT * FROM Description
NATURAL JOIN (
  SELECT One AS name FROM hardest WHERE Two = 'strawberry'
  UNION
  SELECT Two AS name FROM hardest WHERE One = 'strawberry'
) AS t1
NATURAL JOIN (
  SELECT One AS name FROM hardest WHERE Two = 'grapes'
  UNION
  SELECT Two AS name FROM hardest WHERE One = 'grapes'
) AS t2
NATURAL JOIN (
  SELECT One AS name FROM hardest WHERE Two = 'apple'
  UNION
  SELECT Two AS name FROM hardest WHERE One = 'apple'
) AS t3;

UPDATE

The OP updated the question, adding the constraint that the resultset should exclude any fruit that are paired with banana; he then proposed a solution using WHERE name NOT IN ( SELECT ... ), but questioned whether an outer join would be more efficient.

First, the query using an outer join:

SELECT Description.* FROM Description
NATURAL JOIN (
  SELECT One AS name FROM hardest WHERE Two = 'strawberry'
  UNION
  SELECT Two AS name FROM hardest WHERE One = 'strawberry'
) AS t1
NATURAL JOIN (
  SELECT One AS name FROM hardest WHERE Two = 'grapes'
  UNION
  SELECT Two AS name FROM hardest WHERE One = 'grapes'
) AS t2
NATURAL JOIN (
  SELECT One AS name FROM hardest WHERE Two = 'apple'
  UNION
  SELECT Two AS name FROM hardest WHERE One = 'apple'
) AS t3
NATURAL LEFT JOIN (
  SELECT One AS name, TRUE As hasBanana FROM hardest WHERE Two = 'banana'
  UNION
  SELECT Two AS name, TRUE As hasBanana FROM hardest WHERE One = 'banana'
) AS t4
WHERE hasBanana IS NULL;

With respect to which is more efficient, this article compares the different approaches. It states (with respect to [NOT] IN ( SELECT ... ):

Essentially, this is exactly the same plan that LEFT JOIN / IS NULL uses, despite the fact these plans are executed by the different branches of code and they look different in the results of EXPLAIN. The algorithms are in fact the same in fact and the queries complete in same time.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You haven't even considered the table description
This is resulting in an empty set
@AnkitGautam: Was it just because I had apples instead of apple? It's working fine at sqlfiddle.com/#!2/6bfdf/7
0

Hi You have answer of your own query , You have to use IN in place of =. Please check query below...

select * from hardest,description where 
   (one=name and two IN ('apple' , 'strawberry','grapes')) 
   or 
   (one IN ('apple','strawberry' , 'grapes') and two=name)

thanks

Improve this answer

Comments

0

Please make sure that the way iam writing is most efficient.If you have any other way more efficient then please write

 SELECT * FROM Description 
   NATURAL JOIN (   SELECT One AS name FROM hardest WHERE Two = 'strawberry'  
     UNION SELECT Two AS name FROM hardest WHERE One = 'strawberry' ) AS t1
   NATURAL JOIN (   SELECT One AS name FROM hardest WHERE Two = 'grapes'  
     UNION   SELECT Two AS name FROM hardest WHERE One = 'grapes' ) AS t2 
   NATURAL JOIN (   SELECT One AS name FROM hardest WHERE Two = 'apples' 
     UNION   SELECT Two AS name FROM hardest WHERE One = 'apples' ) AS t3
    where name not in ( SELECT One AS name FROM hardest WHERE Two = 'banana' 
     UNION   SELECT Two AS name FROM hardest WHERE One = 'banana') AS fz );
Improve this answer

1 Comment

If anybody is able to do it with left join then please write
0

I'm cleaning out my answer as the question is mutating away from the original question.

Improve this answer

2 Comments

Have you understand my question
It gives all fruits that are paired with ANY of apple, strawberry, or grapes - not those that are paired with ALL of them (which is what your question asked for).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.