Need some kind of join

Kudose

'ello all.

I've got a small problem here. I have two tables (users, usersrating) both with a key on uid

The users table has 5 records, the usersrating table has 3.

What kind of join do I need to get all of the data and preserve the uid column?

I have this SQL

$sql = "SELECT `u`.*, `ur`.* FROM `".PREFIX."users` AS `u` LEFT OUTER JOIN `".PREFIX."usersrating` AS `ur` ";
        $sql .= "ON `u`.`uid` = `ur`.`uid` WHERE `u`.`isactive` = 1 ";

SELECT `u` . * , `ur` . *
FROM `gold_users` AS `u`
LEFT OUTER JOIN `gold_usersrating` AS `ur` ON `u`.`uid` = `ur`.`uid`
WHERE `u`.`isactive` =1
LIMIT 0 , 30

But for some reason (I am no DBA by far) the uid from 2 of the left table records gets replaced with blank values (the ones with no right table matching record).

After jacking with phpMyAdmin and running the queries, I see that uid is set using the second uid found (or not found) in the usersrating table.

Can someone please enlighten me as to how to do this properly? Basically I just need the right table to not overwrite the left table's values when the same column is found.

TIA!

edit

I know I can exclude the column from being returned by changing ur.*, but I want all of the columns (except uid since it is in the left table) ... hence the lazy fetching.

NogDog

Not sure if you want the "OUTER". Maybe just try a "LEFT JOIN"?

malbera

does this help?

$sql = "SELECT `u`.*, `ur`.*, [B]`ur`.uid as ur_uid [/B]....

Kudose

Removing OUTER didn't do anything 🙁

(I thought LEFT OUTER JOIN and LEFT JOIN were synonymous, but like I said, I am no DBA)

Using u.uid AS userid did solve the issue at hand, but I am really interested to see if the problem can be solved using another method.

Thanks for the replies! 🙂

NogDog

Ah, I see what you're talking about now. Perhaps this is another good reason for not using the same name for key columns in different tables? (I normally would call them "table1_id" and "table2_id", so that it's always clear what field I'm referring to.) Otherwise you're probably stuck with using an alias for the one you want to grab. (But like you said, I'm no DBA... 😉 )