Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    Unanswered: [SOLVED] order by common lookup table matches...

    Sorry for the title, might not be very clear.
    Basically I have a table of users (id, name), a lookup table preferences (id, title) and the table of user-pref (idUser, idPref).

    Given a user id X I wanted to select a list of users, ordered by the highest amount of coincidence in the preference table...

    something like

    SELECT * FROM users WHERE id <> X ORDER BY -number of common preferences-

    What would be a good approach?
    ThankS!
    Last edited by diegolaz; 03-14-13 at 10:30. Reason: added solved to title

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Your request is contradicting itself.
    Given a user id X I wanted to select a list of users . . .
    I think you want a list of users, not just a single user. Right?
    If so, then these queries might be helpful.

    If you are really looking just for one user id, then each of the queries would need a WHERE u.user_id = 1 or something like that.

    List All Users and their assigned preferences:
    Code:
    Select u.user_id, u.user_name, p.pref_title
      from users u
     inner join user_prefs up on up.user_id = u.user_id
     inner join prefs p on p.pref_id = up.pref_id
    List Each user and how many preferences they have:
    Code:
    Select u.user_id, u.user_name, COUNT(*)
      from users u
     inner join user_prefs up on up.user_id = u.user_id
     inner join prefs p on p.pref_id = up.pref_id
     group by u.user_id, u.user_name
     order by COUNT(*) desc, u.user_name
    List each preference and how many users have that preference:
    Code:
    Select p.pref_title, COUNT(*)
       from prefs p
      inner join user_prefs up on up.pref_id = p.pref_id
      inner join users u on u.user_id = up.user_id
      group by p.pref_title
      order by COUNT(*) desc
    Last edited by LinksUp; 03-11-13 at 12:21.

  3. #3
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58
    Thanks for your reply!
    I think the closest one is the last one...
    Actually the first line was not a contradiction, but I just didnt explain it well.

    Given a user X, would be like my user when Im logged, I want to get the "top 10" that have the most preferences like me.

    So if user Y has 10 preferences that I also have and user Z has only 4 in common with me, user Y would be higher in the result that Z.

    Does it make sense?

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I understand what you are trying to do, but I am afraid that the solution is beyond my current abilities.

    I am guessing that a CTE would be needed here. I tried to implement one that met your requirements and was able to get some results, but not exactly what you were looking for. You might be able to combine a couple of the individual queries that I posted earlier into a CTE to help you get closer to your solution.

    I'm hoping that someone else will be able to help you.

  5. #5
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58
    thanks a lot for even trying it!
    Yes, I'll probably be better combining some solutions of simpler queries

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The lookup table is superfluous for this problem, since you are only interested in the number of shared preferences, rather than what those specific preferences are.
    The user-pref table alone is sufficient to answer your question, though you'll probably want to join back to the user table in order to get actual names and demographics.

    Code:
    select	top 10
    	A.UserID,
    	B.UserID,
    	COUNT(B.*) as SharedPreferences
    from	UserPreferences as A
    	left outer join UserPreference as B
    		on A.Preference = B.Preference
    		and A.UserID <> B.UserID
    group by A.UserID,
    	B.UserID
    order by COUNT(*) desc
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    excelent!

    Excelent!
    Adding the WHERE clause I can filter it for getting the "ranking" for a specific user.
    Small edit I had to remove the B. in Count(B.*) for it to work also.
    The 5 in WHERE A.UserID = 5 will obviously be set by parameter
    Code:
    select	top 10
    	A.UserID,
    	B.UserID,
    	COUNT(*) as SharedPreferences
    from	UserPreferences as A
    	left outer join UserPreference as B
    		on A.Preference = B.Preference
    		and A.UserID <> B.UserID
    WHERE A.UserID = 5
    group by A.UserID,
    	B.UserID
    order by COUNT(*) desc
    Thanks a lot!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Right.
    Now, if you want a query that gives the top 10 for each user, rather than for a single user, you'll need a CTE...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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