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)
 Compare/match question

Author  Topic 

neutcomp
Posting Yak Master

111 Posts
Posted - 2004-01-20 : 04:47:16
Hello I have alot of memebers in my database.
Now I made a system where you can select words that match your profile. Like "programming", "websites", "reading", "sport", etc!
these words you can place under diverent types like. weak, strong, strongest. So If you dislike sport you place sport in the field weak. Do you really like programming you place it under strongest.

tblkeywords
idUser
idKeyWord
type

1 1 strong
1 2 strongest
1 5 weak
2 1 strong
2 3 weak
3 2 strongest
4 2 strong
4 5 weak
etc.

Now I want to match people with the most similarity types to each other. Can somebody tell me how I can do this?

Thanxx
Bjorn

p.s. if you need some more info just let me know!

mr_mist
Grunnio

1870 Posts
Posted - 2004-01-20 : 06:27:42
On first glance, I'm thinking you may find this easier if you have another table that assigns a numeric value to the "Strong" "Weak" etc categories. You can then use a CASE statement with the difference in numeric value per keyword to determine the match. Yes.

-------
Moo. :)
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2004-01-20 : 06:46:01
Pretty simple.
tblkeywords >> idUser, idKeyWord, typeNo

select t1.idUser idUser1, t2.idUser idUser2
from tblkeywords t1 inner join tblkeywors t2 on
t1.idUser<t2.idUser and t1.idKeyWord=t2.idKeyWord
group by t1.idUser, t2.idUser
having sum(abs(t1.typeNo-t2.typeNo)) <= {here a number from 0 to 20}

table types:
typeNo ~~ type
0 ~~~ disgust
1 ~~~ neutral
2 ~~~ weak
3 ~~~ strong
4 ~~~ strongest
Go to Top of Page

neutcomp
Posting Yak Master

111 Posts
Posted - 2004-01-20 : 08:07:33
Oke thanxx but I dont really understand your query "Stoad", because I want to match let say idUser = 87 with the other users in my database. And I cannot enter an username in your query.

And what happend if there is no one exactly the same so one user has "sport - strong" and the other "sport - strongest" and the rest of the users have "sport - weak". Than I want the user most near also be in the list.

Hope you can help me out,

thanxx
Bjorn
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2004-01-20 : 09:22:08
Less difference of numeric presentations of types - more common in a given interest.
Then I just sum up all these differences (more correctly, their absolute values).
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2004-01-20 : 09:31:16
definitely do not use this "multiple field" method -- use 1 column called "interest", and another for "interest-level", which could be either text or a numeric value. if it is a text value, use another table to list all possible values and perhaps a numeric representation for each.


- Jeff
Go to Top of Page

neutcomp
Posting Yak Master

111 Posts
Posted - 2004-01-20 : 09:59:45
jsmith8858 nice amount of postings :-) 2004 (the year whe live in!)
Yes I made a change now to the database. I have only numbers in my tblkeywords.

Stoad i understand that you sum up all those values but its not correct because let say

User1:
sport - 0 (weak)
computers - 3 (strongest)
Sum = 3

User2:
sport - 3
computers - 0
Sum = 3

So you say that those people have the same interests? I dont think so!
Hope there is an other solution to my problem.

Thanxx
Bjorn
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-01-20 : 10:45:30
A dating service for the Netherlands? Go figure...

http://www.cia.gov/cia/publications/factbook/geos/nl.html

Anyway...you need SUM by catgeory and/or relate categories to each other with a super type...so that you can group like things together..

Ballet, Classical music, jazz, ect group together

Grung, dive bars, clubing, ect group together...

How you make that disticntion is up to you...

What about the cross lifestyle people...



Brett

8-)
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2004-01-20 : 13:13:04
ebs, neutty, did you notice in my dull query this (in ON part):

and t1.idKeyWord=t2.idKeyWord ?

And you say that I compare interest to tequila (cactusovoe poilo)
against interest to dry stoads collecting?

User1:
sport - 0 (weak)
computers - 8 (strongest)
Sum = 8

User2:
sport - 3
computers - 4
Sum = 7

In fact, your sample goes like this: sum = abs(0-3) + abs(8-4) = 7,
and less this sum - more close men in their interests.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-01-20 : 13:29:16
The Freaky Yak Linguist strikes again!

Tara
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2004-01-20 : 13:42:49
ok I just noticed .. I thought that idKeyWords are ids of interests.
Strangely you constructed your table. Let's start from this table:

t: userid, interestid, interestdegree

here interestids are numeric codes of all interests: sport, web and so on,
interestdegrees are numeric codes of all types of interest: weak, strong and so on.

Lets investigate the user with userid=5 (e.g.):

select t2.userid as MatchingUser,
sum(abs(t1.interestdegree-t2.interestdegree)) as HowCloseHeIs
from t t1 inner join t t2 on t1.interestid=t2.interestid
where t1.userid=5 and t2.userid<>5
group by t2.userid
having sum(abs(t1.interestdegree-t2.interestdegree)) <= 3 (for example)
order by 2, 1
Go to Top of Page

neutcomp
Posting Yak Master

111 Posts
Posted - 2004-01-21 : 04:57:39
Oke it works Great!

I only do not understand the result

idUser idKeyWord type
1 2 1
1 1 1
2 3 2
3 3 2
3 2 2
4 1 1

If I do a search for user 1 I get this as result:
MatchUser HowClosHeIs
4 0
3 1

So is user 4 closer or user 3? I think user 4 because the have one exact the same match.(1 - 1)

Can you also limit the amount of results it will give?
Thanxx for all the great help!

Cya
Bjorn
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2004-01-21 : 05:35:11
Hi neut!
Yes, you're right upon the 0 and 4 values of HowCloseHeIs (you may think
of this field as if it is a distance between 2 men). How you can limit the result.
There are two ways:
1. by decreasing the number from HAVING sum(... ...) <= 3 (put here 0
instead of 3 and you'll get all absolute matches of a given user).
2. by adding into the query TOP N predicate, like this: select TOP 4 ... ...
By this you'll get only 4 the most close matches for a user in subject.
Go to Top of Page

neutcomp
Posting Yak Master

111 Posts
Posted - 2004-01-21 : 06:13:09
select TOP4 t2.userid as MatchingUser,
sum(abs(t1.interestdegree-t2.interestdegree)) as HowCloseHeIs
from t t1 inner join t t2 on t1.interestid=t2.interestid
where t1.userid=5 and t2.userid<>5
group by t2.userid
having sum(abs(t1.interestdegree-t2.interestdegree)) <= 3 (for example)
order by 2, 1

This is not working :(

I think Its have to be something with LIMIT 0, 3
Because this works: SELECT * from tblusers LIMIT 0, 3

Thanxx
Bjorn
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2004-01-21 : 07:11:23
Hm.. what does "This is not working" mean? More info need.
Go to Top of Page

neutcomp
Posting Yak Master

111 Posts
Posted - 2004-01-21 : 07:20:30
I get an error that my sql statement is not correct.

hope this makes any sens!
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2004-01-21 : 08:05:17
and what is the error message?
maybe you wrote TOP4 instead of TOP 4?
Go to Top of Page

neutcomp
Posting Yak Master

111 Posts
Posted - 2004-01-21 : 08:37:31
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 't2.idUser AS MatchingUser, SUM(ABS(t1.interestdegree-t2.interestdegree)) AS H

And I wrote in my message TOP4 but in the query its TOP 4

I think Top 4 is not a mysql variable the have the limit 0,4 option.
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2004-01-21 : 08:49:06
neut, old bean, I never saw that MySQL,
not to mention its f**king manual.. lol....
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-01-21 : 12:12:35
neutcomp, this is a SQL Server site. You'll need to find a MySQL forum.

You've currently got 69 posts. Have all of your questions been MySQL related?

Tara
Go to Top of Page

neutcomp
Posting Yak Master

111 Posts
Posted - 2004-01-21 : 13:10:31
But this is the best forum ever!!

Here the help you 100%
No I am not going away, sorry

And the answer fit also mysql, not only sqlserver or something else.
Ofcours not everything works for mysql but I always have simpel question.

Cya
Bjorn
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site