INNER JOIN Help

[stewart715]

I can't seem to figure this out whatsoever! Any help will be much appreciated. To explain best, I'll show you the database tables I have

for example purposes lets say $user->uid is 2

TABLE: buddylist
[b]uid  buddy  received[/b]
2      7          1
3      5          1
2      8          0
2      3          1

TABLE: users
[b]uid  name[/b]
2    mike
7      joe
3    john
8      bill

i need a code that will select buddy from buddylist where $user->uid = uid and receieved = 1 and then check users where the outputted buddy number = uid (in users) then to output the name..

so joe and john should come out..it will see that $user->uid (2) has 7 and 3 next to it in buddy where recieved is 1...then it will find that persons name by comparing 7 and 3  to uid in users and then ouput joe and john..im guessing its an innerjoin? Thanks for ANY help!

[stewart715]

Also, I can't seem to find any tutorials on this

[kirk112]

Something like this?

SELECT b.buddy, u.users FROM buddylist as b LEFT JOIN users AS u ON b.buddy = u.uid WHERE b.uid = '2' AND b.received = '1'


Not sure if that is what you where looking for (untested)

[stewart715]

Yes kind of but i need it to output 'name' from users Thank you

[stewart715]

Also, I'm not sure how to have it make a list of everything..

I know mysql_result would give me just one of the names

how could I make it list the matches? Well, I guess first I need the query code lol. I've been working on this for so long and I can't get it.

[Barand]

try

[code]
SELECT u.name
FROM buddylist b
INNER JOIN users u ON b.buddy = u.uid
WHERE b.received = 1
AND b.uid = 2
[/code]

[stewart715]

Fantastic. Thank you very much (:

Now, what would I do to list the results?

mysql_result doesn't work...

[Barand]

[code]
<?php
$sql = "SELECT u.name
        FROM buddylist b
        INNER JOIN users u ON b.buddy = u.uid
        WHERE b.received = 1
        AND b.uid = 2" ;
$res = mysql_query($sql) or die (mysql_error());
while ($row = mysql_fetch_row($res)) {
    echo $row[0] . '<br />';
}
?>
[/code]

[stewart715]

Edit. nevermind! thats perfect thanks so much!