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
 Transact-SQL (2000)
 match functions with profiles

Author  Topic 

__madmax__
Starting Member

31 Posts
Posted - 2004-10-04 : 18:05:04
getting really frustrated here... :(

i will try to be as clear as possible..

i have two tables;
1. iamProfile (i am profile) and;
2. isProfile ( i search profile).

In both tables i have the same colums;

CREATE TABLE [dbo].[isProfile] (
	[isPid] [int] IDENTITY (1, 1) NOT NULL ,
	[uid] [int] NULL ,
	[vid] [int] NULL ,
	[isAid] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[iamProfile] (
	[iamPid] [int] IDENTITY (1, 1) NOT NULL ,
	[uid] [int] NULL ,
	[vid] [int] NULL ,
	[iamAid] [int] NULL ,
	[txt] [varchar] (250) NULL 
) ON [PRIMARY]
GO


in both tables i have profiles of users;
uid = user id
vid = question id
iamAid, isAid = answer id's

The thing i want to do, is match these tables, and count the times the users matched. A match is true, if user 1 said he is looking for user 2 and user 2 is als looking for user 1 at the same question. That's 1 'hit'
for example

iamProfile
uid    vid    aid
101    50     51
175    50     52

isProfile
uid    vid   aid
101    50    52
175    50    51


that would be one hit.

i came up with this SQL script;


SELECT     TOP 100 PERCENT p2.uid, COUNT(p2.uid) AS score1
FROM         isProfile p1 INNER JOIN
                      iamProfile p2 ON p1.vid = p2.vid AND p1.isAid = p2.iamAid AND p2.uid <> p1.uid
WHERE     (p1.uid = 101)
GROUP BY p2.uid
ORDER BY COUNT(p2.uid) DESC


this counts all the same answers for, in this example, user 101. But this goes only one way, in one table....

i really hope sombody can give me some tips for this problem !

very BIG thnxs in advance...

you can see an example on this site http://www.soapshow.nl/?c=users&s=start for all the questions.

Bitz
Starting Member

19 Posts
Posted - 2004-10-05 : 04:04:01
Two points:

Your example above is actually "two" hits, not one. Could you be a little more specific on the statement "this goes only one way, in one table..." for me?
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-05 : 05:58:54
wel i'll try :)

it's for a match between two people. You can fill out, what you like/are, and you can fill out, what you are looking for.

say i say i like blue, but i am looking for sombody that likes red. So i go loooking in the db, for people that like red, but i also check that the people who are looking for blue, so now there is a 'cross' match or something like that..

does this clarify anything ?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-10-05 : 06:06:32
try this:

SELECT TOP 100 PERCENT p2.uid, COUNT(p2.uid) AS score1
FROM isProfile p1 INNER JOIN
iamProfile p2 ON p1.vid = p2.vid AND p1.isAid = p2.iamAid and (p1.uid > p2.uid)
GROUP BY p2.uid
ORDER BY COUNT(p2.uid) DESC


Go with the flow & have fun! Else fight the flow
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-05 : 06:25:10
hey spirit1,

thnxs for thining about my problem.
mmm tried it... but i think you made a mistake, you are now only looking for users that are greater than.

so i wil start fighting the flow ;)
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-10-05 : 06:32:05
if i understand correctly you basically want to get rid of cross duplicates.
i.e:
1...2
2...1
is one hit

try this
SELECT TOP 100 PERCENT p2.uid, COUNT(p2.uid) AS score1
FROM isProfile p1 INNER JOIN
iamProfile p2 ON p1.vid = p2.vid AND p1.isAid > p2.iamAid and p1.uid <> p2.uid
GROUP BY p2.uid
ORDER BY COUNT(p2.uid) DESC

yeah i choose the wrong columns before

Go with the flow & have fun! Else fight the flow
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-05 : 06:57:48
still fighting... this part; p1.isAid > p2.iamAid has to be p1.isAid = p2.iamAid. Beacause they heave to be equal, but besides that, it has to be equal the other way around. I have the idea that i need to join the table again, to check it the other way around...
Go to Top of Page

Shurgenz
Yak Posting Veteran

51 Posts
Posted - 2004-10-05 : 07:12:02
in the search profile wish you to serch all, who have the same vid or aid? As I see from your previous describe (about red and blue)
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-05 : 07:17:19
i want to search/match people who have the same vid AND aid, but a user fill's out two different profiles. One for who 'i am' an one for what 'i search' for. Then i check if the users profile (iam profile) matches what i am looking for, but at the same question, i want to check if the person, 'is searching' for what 'i am'...

difficult to explain... i try to make a nice drawing :P...
Go to Top of Page

Shurgenz
Yak Posting Veteran

51 Posts
Posted - 2004-10-05 : 07:20:35
or user1(me).vid=user2(not me).aid and user1(me).aid=user2(not me).vid ???

or user1(me).vid=user2(not me).vid and user1(me).aid=user2(not me).aid ???
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-05 : 07:22:19
mmm i think i have it now... 


SELECT     TOP 100 PERCENT p2.uid, COUNT(p2.uid) AS score1
FROM         isProfile p1 INNER JOIN
                      iamProfile p2 ON p1.vid = p2.vid AND p1.isAid = p2.iamAid AND p1.uid <> p2.uid INNER JOIN
                      iamProfile p4 ON p1.uid = p4.uid AND p1.vid = p4.vid INNER JOIN
                      isProfile p3 ON p4.vid = p3.vid AND p4.iamAid = p3.isAid AND p4.uid <> p3.uid
WHERE     (p2.uid = '175')
GROUP BY p2.uid
ORDER BY COUNT(p2.uid) DESC


in this case i am matching the profile of user with uid 175...
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-05 : 07:29:44
maybe i forgot to tell.. but you can have multiple answers with one questions (checkboxes) in you profile; so for the question what's your favorite color you can answer red, blue and gray, so you get;
uid vid aid
101 5 1
101 5 2
101 5 3
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-05 : 07:53:58
mmm i think it has to be like this...

http://soapshow.nl/_test/vb_match.gif

if anybody has any comments, please do comment :P
all input is very welcome..
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2004-10-05 : 08:07:27
it was great you gave us the tables -- can you give us some more sample data (that covers your last example, where they have more than 1 color). please give us INSERT statements.

After giving us insert statements for your two tables, with just enough sample data that covers the various possibilities you'd like to handle, please let us know what the final result should be based on that sample data.

I promise if you do this for us we will give you an answer in no time !

thanks!


- Jeff
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-06 : 05:37:47
too bad, i didn't get a mail about your reply...

here's some sample data to put in


insert into iamProfile(uid, vid, iamAid) values('101', '50','103') 
insert into iamProfile(uid, vid, iamAid) values('101', '51','167') 
insert into iamProfile(uid, vid, iamAid) values('101', '52','122') 
insert into iamProfile(uid, vid, iamAid) values('101', '71','193') 
insert into iamProfile(uid, vid, iamAid) values('101', '71','195') 
insert into iamProfile(uid, vid, iamAid) values('101', '71','196') 
insert into iamProfile(uid, vid, iamAid) values('101', '71','198') 
insert into isProfile(uid, vid, isAid) values('101', '50','103') 
insert into isProfile(uid, vid, isAid) values('101', '50','105') 
insert into isProfile(uid, vid, isAid) values('101', '51','171') 
insert into isProfile(uid, vid, isAid) values('101', '51','175') 
insert into isProfile(uid, vid, isAid) values('101', '51','177') 
insert into isProfile(uid, vid, isAid) values('101', '52','121') 
insert into isProfile(uid, vid, isAid) values('101', '52','150') 
insert into isProfile(uid, vid, isAid) values('101', '71','193') 
insert into isProfile(uid, vid, isAid) values('101', '71','195')
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-06 : 05:49:51
here's some data for another user :P


insert into iamProfile(uid, vid, iamAid) values('175', '50','103')
insert into iamProfile(uid, vid, iamAid) values('175', '51','167')
insert into iamProfile(uid, vid, iamAid) values('175', '52','124')
insert into iamProfile(uid, vid, iamAid) values('175', '71','193')
insert into iamProfile(uid, vid, iamAid) values('175', '71','194')
insert into iamProfile(uid, vid, iamAid) values('175', '71','195')
insert into iamProfile(uid, vid, iamAid) values('175', '71','196')
insert into iamProfile(uid, vid, iamAid) values('175', '71','198')
insert into iamProfile(uid, vid, iamAid) values('175', '71','199')
insert into iamProfile(uid, vid, iamAid) values('175', '71','200')
insert into iamProfile(uid, vid, iamAid) values('175', '71','201')
insert into iamProfile(uid, vid, iamAid) values('175', '71','203')
insert into iamProfile(uid, vid, iamAid) values('175', '71','197')
insert into iamProfile(uid, vid, iamAid) values('175', '71','202')
insert into isProfile(uid, vid, isAid) values('175', '50','103')
insert into isProfile(uid, vid, isAid) values('175', '51','167')
insert into isProfile(uid, vid, isAid) values('175', '51','168')
insert into isProfile(uid, vid, isAid) values('175', '51','169')
insert into isProfile(uid, vid, isAid) values('175', '51','170')
insert into isProfile(uid, vid, isAid) values('175', '51','171')
insert into isProfile(uid, vid, isAid) values('175', '51','172')
insert into isProfile(uid, vid, isAid) values('175', '51','173')
insert into isProfile(uid, vid, isAid) values('175', '51','174')
insert into isProfile(uid, vid, isAid) values('175', '51','175')
insert into isProfile(uid, vid, isAid) values('175', '51','176')
insert into isProfile(uid, vid, isAid) values('175', '51','177')
insert into isProfile(uid, vid, isAid) values('175', '51','178')
insert into isProfile(uid, vid, isAid) values('175', '52','119')
insert into isProfile(uid, vid, isAid) values('175', '52','120')
insert into isProfile(uid, vid, isAid) values('175', '52','121')
insert into isProfile(uid, vid, isAid) values('175', '52','150')
insert into isProfile(uid, vid, isAid) values('175', '52','125')
insert into isProfile(uid, vid, isAid) values('175', '71','193')
insert into isProfile(uid, vid, isAid) values('175', '71','195')
insert into isProfile(uid, vid, isAid) values('175', '71','192')
insert into isProfile(uid, vid, isAid) values('175', '71','201')
insert into isProfile(uid, vid, isAid) values('175', '71','197')
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-06 : 06:00:23
now, with this data, say user 101 filled out question 50. He fills it out 2 times! one time in the IAMProfile table and one tim ein the ISProfile table. Zo let's say with the above data, he answered for iam 103 and for isProfile also 104. (it different then above, but i think it will make it a little clearer)

Now he presses the match button, and the database goed looking for people that,
have in the iamProfile table question 50 and answer 104 and for the same question in the isProfile table answer 103. So in words;
person A likes blue and looks for red
person B likes red and looks for blue

please tell me if it's not clear!
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-10-06 : 08:02:15
based on the data you provided (great by the way) could you tell us what is the desired result, please?

Go with the flow & have fun! Else fight the flow
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-06 : 08:54:46
i think you need the following;

i need a list with uid and 'score', where score is how many times there is a 'match' so, ordered by teh score value.

something like;


uid   score
101   4
97    3
36    2

after that, i also need the username en dateofbirth from the users table in the same row, but the first will be great already!

hope this helps... you can always IM me
Go to Top of Page

VIG
Yak Posting Veteran

86 Posts
Posted - 2004-10-06 : 09:36:48
SELECT p2.uid, COUNT(p2.uid) AS score1
FROM isProfile p1 INNER JOIN
iamProfile p2 ON p1.vid = p2.vid AND p1.isAid = p2.iamAid AND p1.uid <> p2.uid
--INNER JOIN
-- iamProfile p4 ON p1.uid = p4.uid AND p1.vid = p4.vid
--INNER JOIN
-- isProfile p3 ON p4.vid = p3.vid AND p4.iamAid = p3.isAid AND p4.uid <> p3.uid
--WHERE (p2.uid = '175')
GROUP BY p2.uid
ORDER BY COUNT(p2.uid) DESC

???
Go to Top of Page

__madmax__
Starting Member

31 Posts
Posted - 2004-10-06 : 10:36:43
hey VIG,

thanxs for your solution, it almost works...
i added a little bit > COUNT (distinct p2.uid) (added the distinct)

but it still matches one way.

If you take a look at this picture www.soapshow.nl/matchForm1.gif, you see two users logged in (user A and B) user B get's 1 match, but that's not correct for me. I want to also want to check if the user that he matches with (user A) is looking for user B. But as you can see, on both profiles there's only one side filled out...

if you look at www.soapshow.nl/matchForm2.gif you can see they both filled out both sides, and that's one match.

am i clear/do you understand what i am trying to tell ?
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