Table matching (tricky perhaps)

Question

Table matching (tricky perhaps)

Thomas Wareham

SSC Veteran

Points: 219
More actions
October 13, 2007 at 5:14 am

#178384

Here's one for you experts (or intermediates, I'm not really sure how difficult this is):
I have two tables, in one is stored information about people's profiles (e.g. eye color, hair color, etc), in a second table are stored the attributes of their desired partner (the type of thing you would input into an online dating website).
What I want to do is match ALL (except for usernames, firstnames, lastnames, and a couple of irrelevant attributes) fields in the desired partner table to the ones in the profiles table.
Then I want to display those matches, and also make an extra column which says how many of the attributes matched (so some sort of count?). Hence I could then list all the "Matches" in descending order by most attributes matched to least attributes matched (i.e. if eye colour, hair colour, body type, etc match with one person thats 3 attributes and say goes on top of the list, BUT if only eye colour matched, thats 1 attribute and goes on the bottom of the list).
Also ideally I don't really want to display the number of attributes that matched (I do for checking purposes) but when a user sees his potential matches they wouldn't be presented with this column.
All help is appreciated, thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thomas Wareham SSC Veteran Points: 219 More actions · Answer 1

Okay, no one's replying. But I'm almost there, just did it using a FULL OUTER JOIN (I think its working okay, but I don't have much dummy data in my tables to be 100% sure).

The question still remains for anyone who knows, is how to count up the number of columns that matched exactly during the FULL OUTER JOIN.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 2

CASE WHEN a.col1 = b.col1 THEN 1 ELSE 0 END

+ CASE WHEN a.col2 = b.col2 THEN 1 ELSE 0 END

+ CASE WHEN a.col3 = b.col3 THEN 1 ELSE 0 END

+ CASE WHEN a.col4 = b.col4 THEN 1 ELSE 0 END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thomas Wareham SSC Veteran Points: 219 More actions · Answer 3

Thanks for the reply Jeff, is there any way to do it using simple Count functions?

Because I'll have to write a function/stored procedure to use the CASEs won't I?

Thomas Wareham SSC Veteran Points: 219 More actions · Answer 4

Im finding it difficult to implement that case structure into a select statement. Im trying

DECLARE @ColumnMatches INT

SELECT a.Username, a.Gender, a.Photograph, a.Description_text, a.Voice_Message, @ColumnMatches

AS "Matches",

CASE "Matches"

WHEN a.Age_Range = p.Age_Range THEN 1

WHEN a.Relationship_status = p.Relationship_status THEN 2 ELSE 0 END

FROM AllProfiles a FULL OUTER JOIN PreferredPartnerProfiles p ON a.Age_Range = p.Age_Range

AND a.Relationship_status = p.Relationship_status

/*This says, select the gender that is preferred by the user Username in his preferredpartner

table.*/

WHERE a.Gender =

(SELECT Gender FROM PreferredPartnerProfiles WHERE Username = 'user1');

But yeah I'm getting different errors, because the SQL syntax is wrong, can someone help further on this?

Thomas Wareham SSC Veteran Points: 219 More actions · Answer 5

Actually, I think I got this thing working now, using Jeff's advised code with a bit of ingenuity. I think this post is done, unless I develop an extended problem to do with the query.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 6

When you get what you want, please post your solution. Thanks. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)