To fetch common values between two rows

[sonysunny]

Suppose a table contains fields such as member_id,item1,item2 and item3.Then how can we fetch common items between two rows.

eg:- mem_id item1 item2 item3
1 a b c
2 b c d
3 d f g

common items between first 2 rows is 'b' and 'c'.
How can we write a query for this.

thanks in advance

[radcaesar]

Suppose a table contains fields such as member_id,item1,item2 and item3.Then how can we fetch common items between two rows.

eg:- mem_id item1 item2 item3
1 a b c
2 b c d
3 d f g

common items between first 2 rows is 'b' and 'c'.
How can we write a query for this.

thanks in advance

What is your exact requirement ? Which two colums you want to compare if the table have 100 records ?

[Brad Orders]

It's a very unusual requirement. Could we perhaps know what the table is for?

At this stage, my first reaction would be to see if the columns item1, item2, etc would be more appropriate as rows in a different table. Then the query becomes easier, and the database schema will meet your needs better.

[sonysunny]

It's a very unusual requirement. Could we perhaps know what the table is for?

At this stage, my first reaction would be to see if the columns item1, item2, etc would be more appropriate as rows in a different table. Then the query becomes easier, and the database schema will meet your needs better.

Thanku for your attention.
I'll explain with another eg.

If i have a 2 tables first with member_id,mem_name and location and another
with member_id, interest1,interest2 and interest3 as fields.
Suppose the tables contains 1000 rechords.
If a new member came , i want to compare that member's interests with all the members in the table and return the number of common interests in each rechord.

[jagged]

Brad is right, you really need to change your schema and store the interests as rows in a different table, ie

mem_id, interest
1, golf
1, tennis
2, polo
2, tennis

then it's easier to extract info afterwards or even to have more than 3 interests later.

If you really can't change it, then you can do something like

SELECT mem_id
FROM
(
SELECT mem_id, interest1 as [Interest] FROM table2
UNION SELECT mem_id, interest2 as [Interest] FROM table2
UNION SELECT mem_id, interest3 as [Interest] FROM table2
) tmp
WHERE interest = 'whatever'

but that's really not the right way to do things.

[Brad Orders]

Yes, I agree with Jagged. You can use his script to simulate the answer you want, but you really, really should change your table structures if at all possible. Your efforts now will save you a lot of work in the future. Good luck.