By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,677 Members | 1,882 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,677 IT Pros & Developers. It's quick & easy.

To fetch common values between two rows

P: 6
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
Feb 22 '08 #1
Share this Question
Share on Google+
5 Replies


radcaesar
Expert 100+
P: 759
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 ?
Feb 22 '08 #2

Brad Orders
P: 21
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.
Feb 23 '08 #3

P: 6
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.
Feb 23 '08 #4

P: 23
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.
Feb 23 '08 #5

Brad Orders
P: 21
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.
Feb 24 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.