Professional Web Applications Themes

too smart for my present sql knowledge... need advice - MySQL

I have 2 tables 'users' and 'relationships' I am doing the following select : SELECT relationships.friend_id, relationships.befriender_id, myfriends.pseudo, friendof.pseudo FROM relationships INNER JOIN users AS myfriends ON relationships.friend_id = myfriends.id INNER JOIN users AS friendof ON relationships. befriender_id = friendof.id WHERE relationships.friend_id = 22 OR relationships.befriender_id = 22 so I get 2 id's and 2 pseudo's in 2 columns 'myfriends.pseudo' et'friendof.pseudo' relationships.friend_id relationships.befriender_id myfriends.pseudo friendof.pseudo 81 22 jece867 aabe307 28 22 pige540 aabe307 88 22 cin82 aabe307 31 22 crece705 aabe307 26 22 crome324 aabe307 22 11 aabe307 chege971 22 13 aabe307 nade6 22 17 aabe307 pres534 22 25 aabe307 ...

  1. #1

    Default too smart for my present sql knowledge... need advice

    I have 2 tables 'users' and 'relationships'

    I am doing the following select :

    SELECT relationships.friend_id, relationships.befriender_id,
    myfriends.pseudo, friendof.pseudo
    FROM relationships
    INNER JOIN users AS myfriends ON relationships.friend_id = myfriends.id
    INNER JOIN users AS friendof ON relationships. befriender_id = friendof.id
    WHERE relationships.friend_id = 22 OR relationships.befriender_id = 22


    so I get 2 id's and 2 pseudo's in 2 columns 'myfriends.pseudo'
    et'friendof.pseudo'

    relationships.friend_id relationships.befriender_id myfriends.pseudo
    friendof.pseudo
    81 22 jece867 aabe307
    28 22 pige540 aabe307
    88 22 cin82 aabe307
    31 22 crece705 aabe307
    26 22 crome324 aabe307
    22 11 aabe307 chege971
    22 13 aabe307 nade6
    22 17 aabe307 pres534
    22 25 aabe307 jond897


    but I would like to get the following results after the query

    contact_id contact_pseudo
    81 jece867
    28 pige540
    88 cin82
    31 crece705
    26 crome324
    11 chege971
    13 nade6
    17 pres534
    25 jond897

    is it possible ? how shoudl I rewrite my select ?

    joss

    Josselin Guest

  2. #2

    Default Re: too smart for my present sql knowledge... need advice

    Josselin wrote: 

    I haven't had a really good study, but at first glance it would seem that
    you could do this by splitting the query into 2 and UNIONing them thus:

    SELECT relationships.friend_id,
    myfriends.pseudo
    FROM relationships
    INNER JOIN users AS myfriends ON relationships.friend_id = myfriends.id
    INNER JOIN users AS friendof ON relationships. befriender_id = friendof.id
    WHERE relationships.friend_id = 22 OR relationships.befriender_id = 22
    UNION
    SELECT relationships.befriender_id,
    friendof.pseudo
    FROM relationships
    INNER JOIN users AS myfriends ON relationships.friend_id = myfriends.id
    INNER JOIN users AS friendof ON relationships. befriender_id = friendof.id
    WHERE relationships.friend_id = 22 OR relationships.befriender_id = 22


    Paul Guest

  3. #3

    Default Re: too smart for my present sql knowledge... need advice

    On 2007-02-23 14:04:49 +0100, "Paul Lautman"
    <com> said:
     
    >
    > I haven't had a really good study, but at first glance it would seem that
    > you could do this by splitting the query into 2 and UNIONing them thus:
    >
    > SELECT relationships.friend_id,
    > myfriends.pseudo
    > FROM relationships
    > INNER JOIN users AS myfriends ON relationships.friend_id = myfriends.id
    > INNER JOIN users AS friendof ON relationships. befriender_id = friendof.id
    > WHERE relationships.friend_id = 22 OR relationships.befriender_id = 22
    > UNION
    > SELECT relationships.befriender_id,
    > friendof.pseudo
    > FROM relationships
    > INNER JOIN users AS myfriends ON relationships.friend_id = myfriends.id
    > INNER JOIN users AS friendof ON relationships. befriender_id = friendof.id
    > WHERE relationships.friend_id = 22 OR relationships.befriender_id = 22[/ref]

    thanks, I'll try.. in the meantime is it possible to use a conditional
    select like that :

    SELECT relationships.friend_id,
    relationships.befriender_id,
    relationships.connection_type,
    relationships.activated_at,
    myfriends.pseudo,
    friendof.pseudo,

    CASE relationships.friend_id = 22
    WHEN 1THEN myfriends.pseudo
    ELSE friendof.pseudo
    END AS contact_pseudo,

    FROM relationships
    INNER JOIN users AS myfriends ON relationships.friend_id = myfriends.id
    INNER JOIN users AS friendof ON relationships. befriender_id = friendof.id
    WHERE relationships.friend_id = 22 OR relationships.befriender_id = 22


    Josselin Guest

  4. #4

    Default Re: too smart for my present sql knowledge... need advice

    On 2007-02-23 14:04:49 +0100, "Paul Lautman"
    <com> said:
     
    >
    > I haven't had a really good study, but at first glance it would seem that
    > you could do this by splitting the query into 2 and UNIONing them thus:
    >
    > SELECT relationships.friend_id,
    > myfriends.pseudo
    > FROM relationships
    > INNER JOIN users AS myfriends ON relationships.friend_id = myfriends.id
    > INNER JOIN users AS friendof ON relationships. befriender_id = friendof.id
    > WHERE relationships.friend_id = 22 OR relationships.befriender_id = 22
    > UNION
    > SELECT relationships.befriender_id,
    > friendof.pseudo
    > FROM relationships
    > INNER JOIN users AS myfriends ON relationships.friend_id = myfriends.id
    > INNER JOIN users AS friendof ON relationships. befriender_id = friendof.id
    > WHERE relationships.friend_id = 22 OR relationships.befriender_id = 22[/ref]

    WUNDEBAR... WONDERFUL ... thanks a lot.. you go it right....

    joss

    Josselin Guest

Similar Threads

  1. I got no knowledge about html...
    By Archilles in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: August 10th, 11:59 AM
  2. Knowledge Wright to CF ?
    By crayongal in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 16th, 08:28 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139