how to write query for retrive common data

Question

Possible Duplicate:
Intersect in mysql

I have do a php project for retrieving common data.I am little weak in writing MySQL query. i was tried different ways .But i cannot get proper output

My table is

 Id  Product-1   product-2
 -------------------------
 1      A           B
 2      B           C
 3      C           A
 4      E           B
 5      H           C

This is my table .I want to write a MySQL query retrieve common elements. eg: Here A,B be will comes to Product-1,Product-2, I want to retrieve common element element of A,B Here The output is C

    3      C           A
    2      B           C

both row can contain either A,B in Product-1 or product-2

Same like A,c common element is B .How to write Query for this... in sql we can use Intersect operation But MySQL i don't have any idea .Please help me...

Possible duplicates: http://stackoverflow.com/questions/2621382/intersect-in-mysql , http://stackoverflow.com/questions/2300322/intersect-in-mysql — Dan, Feb 21 '12 at 14:37
Please can you try and clarify your request, personally at least I can't really see what you are asking for so can't assist — Simon at The Access Group, Feb 21 '12 at 14:39

Marcus Adams · Answer 1 · 2012-02-21T18:49:46.537

What I would recommend, instead of using the columns for two way relationships, which complicates the query, keep the relationships one way.

So, instead of the following meaning A to C and C to A:

Id  Product-1   Product-2
 -------------------------
 3      C           A

You actually do this to mean A to C and C to A:

Id  Product-1   Product-2
 -------------------------
 2      A           C
 3      C           A

Then, assuming no duplicates, your query is simply this:

SELECT Product-1
FROM tablename
WHERE Product-2 IN ('A', 'B')
GROUP BY Product-1
HAVING COUNT(*) = 2

However, with your data, the following query will give the common elements for 'A', 'B':

SELECT Product-1 FROM
((SELECT Product-1, Product-2
 FROM tablename)
UNION
(SELECT Product-2, Product-1
 FROM tablename)) t
WHERE Product-2 IN ('A', 'B')
GROUP BY Product-1
HAVING COUNT(*) = 2

What I've done is create a derived table that has a copy of the relationships reversed, so that I get the relationships to be one way, and then proceed normally.

how to write query for retrive common data

1 Answers1