[SOLVED] Semi-Complex Query

[cmgmyr]

Hey all, I have a little bit of an issue with a query. I have 2 tables users and network

 

users:

------

userid

username

main_image

level

...

 

network:

------

nid

userid

friendid

ap

 

When a user adds another user into their network the sender is userid and the reciever is friendid. When it is approved each person shows up in one another's network. So the users.userid can either be network.userid or network.friendid

 

Here is what I want...1 query that outputs the username, main_image, level, and nid of the other person in the network.

 

Here is what I have so far:

SELECT n.nid, n.userid, n.friendid, u.username, u.level, u.main_image FROM network n LEFT JOIN users u on (u . userid = n . userid) WHERE (n.friendid = $userid OR n.userid = $userid) AND ap = 1

 

The problem is, is that if mine is the userid it shows my name and main_image. Is there a way to "scan" either userid or friendid and take the oposite one? I think I'm pretty close here.

 

Thanks!

-Chris

[bubblegum.anarchy]

maybe this:

 

SELECT network.nid
     , network.userid
     , network.friendid
     , IF (owner.userid IS NULL, friend.username, owner.username) AS username
     , IF (owner.userid IS NULL, friend.level, owner.level) AS level
     , IF (owner.userid IS NULL, friend.main_image, owner.main_image) AS main_image
FROM network
     LEFT JOIN users owner ON network.userid = owner.userid
     LEFT JOIN users friend ON network.userid = friend.userid) 
WHERE (n.friendid = $userid OR n.userid = $userid) AND ap = 1

[cmgmyr]

hey thanks for the reply. I just had to change your uery around for it to "work" but I'm still getting the same results.

 

SELECT network.nid
     , network.userid
     , network.friendid
     , IF (owner.userid = 1, friend.username, owner.username) AS username
     , IF (owner.userid = 1, friend.level, owner.level) AS level
     , IF (owner.userid = 1, friend.main_image, owner.main_image) AS main_image
FROM network
     LEFT JOIN users owner ON network.userid = owner.userid
     LEFT JOIN users friend ON network.userid = friend.userid 
WHERE (network.friendid = 1 OR network.userid = 1) AND ap = 1

 

I'm on MySQL 4.1.14 if that helps

[bubblegum.anarchy]

SELECT network.nid
    , network.userid
    , network.friendid
    , if (network.userid = 1, friend.username, owner.username) AS username
    , if (network.userid = 1, friend.main_image, owner.main_image) AS main_image
    , if (network.userid = 1, friend.level, owner.level) AS level
FROM network
    INNER JOIN users AS owner ON network.userid = owner.userid
    INNER JOIN users AS friend ON network.friendid = friend.userid 
WHERE ( network.userid = 1 OR network.friendid = 1 ) AND ap = 1;

 

SELECT network.nid
    , network.userid
    , network.friendid
    , if (network.userid = $userid, friend.username, owner.username) AS username
    , if (network.userid = $userid, friend.main_image, owner.main_image) AS main_image
    , if (network.userid = $userid, friend.level, owner.level) AS level
FROM network
    INNER JOIN users AS owner ON network.userid = owner.userid
    INNER JOIN users AS friend ON network.friendid = friend.userid 
WHERE ( network.userid = $userid OR network.friendid = $userid ) AND ap = $ap;

 

EDIT: though the query looks a lot different the primary problem was network.userid in the second LEFT JOIN should be network.friendid and I figured an INNER JOIN is more appropriate since both ids are required.

[cmgmyr]

wow, that worked like a charm! Thanks for the help!