too smart for my present sql knowledge... need advice
This is a discussion on too smart for my present sql knowledge... need advice within the MySQL Database forums, part of the Database Forums category; I have 2 tables 'users' and 'relationships' I am doing the following select : SELECT relationships.friend_id, relationships.befriender_id, myfriends.pseudo, ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-23-2007
|
|||
|
|||
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 
|
|
02-23-2007
|
|||
|
|||
|
Re: too smart for my present sql knowledge... need advice
Josselin wrote:
> 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 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
|
|
02-23-2007
|
|||
|
|||
|
Re: too smart for my present sql knowledge... need advice
On 2007-02-23 14:04:49 +0100, "Paul Lautman"
<paul.lautman@btinternet.com> said: > Josselin wrote: >> 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 > > 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 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
|
|
02-23-2007
|
|||
|
|||
|
Re: too smart for my present sql knowledge... need advice
On 2007-02-23 14:04:49 +0100, "Paul Lautman"
<paul.lautman@btinternet.com> said: > Josselin wrote: >> 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 > > 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 WUNDEBAR... WONDERFUL ... thanks a lot.. you go it right.... joss
|
Linear Mode
