How to Compare fields of two different 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

General SQL Server Forums

New to SQL Server Programming

How to Compare fields of two different rows

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2009-08-05 : 02:22:43

Hi Friends

I have two tables named User and Events

User Tables has Fields
UserId Interests
1 2,3,4,10
2 1,5,6,9
3 0,11,12,6,9,8

Events Table has Fields
EventId Interests
1 0,8,6,7
2 1,9,12,15,10
3 8,9,6,5,2

In user table userid 1 has interests in the following numbers
2,3,4,10 likewise for userid 2 and userid 3

In events table eventid 1 is shown to the users who are all having interests in any one of the numbers 0,8,6,7
Samething for eventid 2 and 3

so when user 1 logs in i need to select the eventids 2,3
Here we need to take the interests of userid 1 (i,e) 2,3,4,10 and we need to compare the each number in event table of interests
so 2 is in eventid 3,3 and 4 is not present in event table ,10 is in eventid 2 ,so i need event id 2 and 3 row as result.

Expected OutPut

EventId Interests
2 1,9,12,15,10
3 8,9,6,5,2

Please help me to get the result

Thanks in Advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-05 : 06:00:22

1 Read about normalization
2 select e.* from users as u inner join events as e on ','+u.interests+',' like '%,'+cast(eventid as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

Kotti
Posting Yak Master

129 Posts

Posted - 2009-08-05 : 06:33:49

quote:
Originally posted by madhivanan

1 Read about normalization
2 select e.* from users as u inner join events as e on ','+u.interests+',' like '%,'+cast(eventid as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

Hi Madhi

Thanks for your reply

I used your query but i can't able to get the correct answer

Please help me to get the correct result.

Your help will be much appreciated.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-05 : 08:25:15

select e.* from users as u inner join events as e on ','+u.interests+',' like '%,'+cast(eventid as varchar(10))+',%' where u.userid=1

Madhivanan

Failing to plan is Planning to fail

Kotti
Posting Yak Master

129 Posts

Posted - 2009-08-05 : 08:49:32

quote:
Originally posted by madhivanan

select e.* from users as u inner join events as e on ','+u.interests+',' like '%,'+cast(eventid as varchar(10))+',%' where u.userid=1

Madhivanan

Failing to plan is Planning to fail

Hi Madhi

Once again thanks for your reply
I tried Your above query already ,but i am not getting the correct result

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-05 : 08:52:43

declare @User Table(UserId int, Interests varchar(100))
insert into @User
select 1, '2,3,4,10' union all
select 2, '1,5,6,9' union all
select 3, '0,11,12,6,9,8'

declare @Events Table (EventId int,Interests varchar(100))
insert into @Events
select 1, '0,8,6,7' union all
select 2, '1,9,12,15,10' union all
select 3, '8,9,6,5,2'

select e.* from @user as u inner join @events as e on ','+u.interests+',' like '%,'+cast(eventid as varchar(10))+',%'
where u.UserId=1

Madhivanan

Failing to plan is Planning to fail

Kotti
Posting Yak Master

129 Posts

Posted - 2009-08-05 : 09:34:45

Hi Madhi

I am getting the correct result when i execute your query .

But for the below query ,i am getting wrong answer.For that i need the eventid 1 details also
since 10 is there.

declare @User Table(UserId int, Interests varchar(100))
insert into @User
select 1, '2,3,4,10' union all
select 2, '1,5,6,9' union all
select 3, '0,11,12,6,9,8'

declare @Events Table (EventId int,Interests varchar(100))
insert into @Events
select 1, '0,8,6,7,10' union all
select 2, '1,9,12,15,10' union all
select 3, '8,9,6,5,2'

select e.* from @user as u
inner join @events as e on ','+u.interests+',' like '%,'+cast(eventid as varchar(10))+',%'
where u.UserId=1

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-05 : 12:09:10

The query is all wrong. You need to compare Intrests to Intrests not Intrests to EventIDs.

I think you'll need a string parsing function (there are several you can choose from). Soemthing like this should work:

SELECT *
FROM 
	dbo.fnc_ParseString((SELECT Interests FROM @User WHERE UserID = 1), ',') AS A
INNER JOIN
	@Events AS E
	ON ','+ E.interests + ',' LIKE '%,' + A.StringValue + ',%'

Kotti
Posting Yak Master

129 Posts

Posted - 2009-08-06 : 01:39:54

quote:
Originally posted by Lamprey

The query is all wrong. You need to compare Intrests to Intrests not Intrests to EventIDs.

I think you'll need a string parsing function (there are several you can choose from). Soemthing like this should work:
SELECT *
FROM
dbo.fnc_ParseString((SELECT Interests FROM @User WHERE UserID = 1), ',') AS A
INNER JOIN
@Events AS E
ON ','+ E.interests + ',' LIKE '%,' + A.StringValue + ',%'

Hi

Thanks for your reply

I am getting error when i execute your query.

Thanks in Advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-06 : 02:14:48

quote:
Originally posted by Lamprey

The query is all wrong. You need to compare Intrests to Intrests not Intrests to EventIDs.

I think you'll need a string parsing function (there are several you can choose from). Soemthing like this should work:
SELECT *
FROM
dbo.fnc_ParseString((SELECT Interests FROM @User WHERE UserID = 1), ',') AS A
INNER JOIN
@Events AS E
ON ','+ E.interests + ',' LIKE '%,' + A.StringValue + ',%'

You should also apply the same function to @Events table too

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources