To fetch common values between two rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

To fetch common values between two rows

Author

Topic

sonysunny
Starting Member

6 Posts

Posted - 2008-02-22 : 01:55:42

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 02:56:19

try this:-

SELECT @Row1 AS Row1,
       @Row2 AS Row2,
       LTRIM(RTRIM(CASE WHEN t1.item1=t2.item1 
                 OR t1.item1=t2.item2 
                 OR t1.item1 =t2.item3
            THEN t1.item1 
            ELSE ''
       END + ' ' +
       CASE WHEN t1.item2=t2.item1 
                 OR t1.item2=t2.item2 
                 OR t1.item2 =t2.item3
            THEN t1.item2 
            ELSE ''
       END + ' ' +
       CASE WHEN t1.item3=t2.item1 
                 OR t1.item3=t2.item2 
                 OR t1.item3 =t2.item3
            THEN t1.item3 
            ELSE ''
       END)) AS 'Common Values'
FROM Table t1
CROSS JOIN Table t2
WHERE t1.mem_id=@Row1
AND t2.mem_id=@Row2

@Row1 and @Row2 are PK of rows you want to compare (i guess thats mem_id field)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-22 : 03:00:27

what's the required output ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources