Trying to "JOIN" 2 tables?!?!

Databases
1

I’m trying to make this work:

$sql = "SELECT
            u.new_userid
          , u.fname
          , u.lname
          , u.email
          , u.img_small
          , u.seouname
        FROM fb_users u
        INNER JOIN fb_user_friends uf
        ON uf.user = u.new_userid
        WHERE u.new_userid <> $profileUserID
        UNION
        SELECT
            u.new_userid
          , u.fname
          , u.lname
          , u.email
          , u.img_small
          , u.seouname
        FROM fb_users u
        INNER JOIN fb_club_users cu
        ON cu.new_userid = u.new_userid
        INNER JOIN
          (SELECT
               clubid
           FROM fb_club_users
           WHERE new_userid = $profileUserID
          ) AS c
        ON c.clubid = cu.clubid
        WHERE u.new_userid <> $profileUserID";

It allmost works like a charm but… In this part:

INNER JOIN fb_user_friends uf
ON uf.user = u.new_userid
WHERE u.new_userid <> $profileUserID

… it goes wrong.

The table fb_user_friends looks like this:
id, user, friend

Now I only want to get friend out of the table where the user i the current “$profileUserID”, but as it is now, it pulls all the “friend” records… Where do I go wrong?

Thanks in advance :slight_smile:

2

let’s start here –

  FROM fb_users u
...
 WHERE u.new_userid <> $profileUserID

this says “go get ~all~ users except for this one”

and then for each user, you do this –

INNER 
  JOIN fb_user_friends uf         
    ON uf.user = u.new_userid

which basically says “get all friends of all users (except for that one)”

could you perhaps explain in words what you actually wanted?

:slight_smile:

3

Well, this gets all the users of a group the current user is a member of except the current user… This is working just fine :wink:

This part is not working correct… I want to get all friends from this table where the “user” is the current user…

4

that’s the part that doesn’t make sense

by the way, i’m looking only at the first SELECT in the UNION, the group stuff will have to come later

let’s consider some sample data

users
tom
dick
harry
curly
larry
moe

friends
tom dick
tom harry
curly larry
moe larry

let’s say that $profileUserID equals curly

so the first part of the join (in the first SELECT), where you want user <> ‘curly’, that will retrieve

tom
dick
harry
larry
moe

then for ~each~ of these, you are joining to the friends table, and the result of this join is

tom dick
tom harry
moe larry

note that dick and larry were eliminated from the inner join, because each has no friends

did you follow that?

5

So, I have to get users friends first like this before the UNION:

SELECT friend FROM fb_user_friends WHERE user = $profileUserID

But that would give a problem with different number of columns?!?

6

maybe we could step back just a tiny bit and start over

what is the entire ~first~ query supposed to do?

in words, please

also, are you storing two rows for each relationship, or is each relationship uni-directional?

in other words, if humpty is friends with dumpty, how do you decide whether humpty goes into the user column and dumpty in the friend column, or vice versa? or both?

7

what is the entire ~first~ query supposed to do?

in words, please

The first query, before UNION, is supposed to get all the current user friends from the table fb_user_friends where the current user is listed as “user”.

are you storing two rows for each relationship, or is each relationship uni-directional?

As it is now, I’m storing two records for each relationship, but I guess I could just use one!? It is on a trial basis so it could be changed with no fuss if it helps the process…

8

here ya go…

SELECT u.new_userid
     , u.fname
     , u.lname
     , u.email
     , u.img_small
     , u.seouname         
  FROM fb_user_friends AS uf
INNER
  JOIN fb_users AS u
    ON u.new_userid = uf.friend
 WHERE uf.user = $profileUserID

somewhat different from your first query, yes?

can you do the second query?

that’s fine, that’s actually more efficient for the queries

9

Thanks my man… That did the trick. You are the best :slight_smile: