SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Canada
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Buddy list thingie... part II...

    Okay. I have one table called USERS, with fields like id, and then a bunch of "contact info" fields... just their profile really. Then I ahve another table called id_links, which has two columns -- id1 and id2...

    Say I have 10 users... in users, id's 1-10...

    In id_links, say I have:
    1|2
    1|3
    4|1
    1|5
    6|1
    7|1
    8|1
    1|9
    10|1

    As you can see 1 is linked to all other 9 ids.. but it occurs as both id1 or id2...

    What I want to do is grab all "profiles" for each of the 9 users who are linked to id 1.. .so, that is id's 2-10... but I have to determine whether it is id1 or id2 that i want to get the info for.. how could I accomplish this? I just can't seem to build a query to do this...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select id
         , foo
         , bar 
      from users
     where id in
           ( select id1 from id_links
              where id2 = 1
             union
             select id2 from id_links
              where id1 = 1
           ) as buddies
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    UK
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the above query will only work if you have MYSQL 4.1.x installed

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, but 4.1 has been in production status since october



    Code:
    select distinct
           id
         , foo
         , bar 
      from users
    inner
      join id_links
        on id = id2 and id1 = 1
        or id = id1 and id2 = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Canada
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No MySQL 4.1.x... hehe, so.. is there a way to do it with 3.x?

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That was the way Rudy did it in his second example. No subquery thus 4.0 and lower can be used.

  7. #7
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Canada
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the meantime I managed to create my own query which seems to work fine... could someone tell me which would probably be better to use? (I don't have a hugely populated database nor a complete script to determine which would be more efficient..)

    Code:
    SELECT DISTINCT(u.id), u.*, UNIX_TIMESTAMP(u.dob) AS dob,
    UNIX_TIMESTAMP(u.last_update) AS last_update FROM users AS u,
    id_links AS i WHERE (i.id1=$userid OR i.id2=$userid) AND
    u.id<>$userid ORDER BY u.name_last ASC
    Last edited by kandie; Feb 5, 2005 at 13:41. Reason: Formatting more nicely...

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's not going to work corrrectly (it will run, but the results will be wrong) because you're missing a join condition

    it's going to join every user which isn't $userid (and there are N-1 of these) to every id_links row where either id1 or id2 is $userid

    not at all what you want

    also, just a note in passing, DISTINCT is not a function, it applies to all columns in the SELECT list
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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