Results 1 to 2 of 2

Thread: Self-join with ORDER BY on a denormalized table

  1. #1
    Join Date
    Sep 2007
    Posts
    2

    Self-join with ORDER BY on a denormalized table

    I know just enough about SQL to be dangerous, and that's mostly 5 years old, but it's time to get my feet wet again, and look! here's a practical project to do it with.

    I'm trying to set up a matching system, where users can identify themselves, and in their user record, specify a list of 6 cities, in descending order of preference.

    Once I have a database, I want any given user to be able to pull up a list of other users who match him or her, where the list is ordered in ascending order by the sum of the indexes of the pseudo-array that those 6 fields comprise in each user's record.

    I'm sure this would probably be easier to query if I normalized out the preferences, but then the UI would be a bunch more difficult to write (I'm using a framework, and I haven't yet learned all of *its* idiosyncracies yet).

    Is there anyway to write this query?

    The rows will look something like

    userid char(6)
    loc1 char(3)
    loc2 char(3)
    loc3 char(3)
    loc4 char(3)
    loc5 char(3)
    loc6 char(3)

    And the order I want out is if the current user matches his loc1 to the other user's loc4, then the value for the ORDER BY should be 5.

    Can I do this with AS clauses and local variables in the query? Or should I just break down and push the preferences into another table?

  2. #2
    Join Date
    Sep 2007
    Posts
    2
    On re-reading that, I see that I wasn't quite clear enough: two users are considered matched if *any* of user1's cities match *any* of user2's cities, and that match should rank as the highest of the pertinent match-goodness-numbers.

    Now I should *really* use a sub table, shouldn't I? :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •