[RESOLVED] query help - select name based on id

[Kudose]

Hello all. I am basically trying to get a parent's username based on the parent id in one query.

Here is what I have, but parentname returns null. Could someone please help?

$query = "SELECT u.uid, u.parentid AS parentID, u.username, u.userlevel, (SELECT username FROM ".PFX."users WHERE uid=parentID) AS parentname FROM ".PFX."users AS u";

TIA!

[rulian]

I think your looking for a JOIN

$query = "
SELECT
u.uid, u.parentid AS parentID, u.username, u.userlevel, parentname.username

FROM ".PFX."users AS u
JOIN ".PFX."users AS parentname ON parentname.uid=u.parentid
";

[Kudose]

Thanks!

I thought I should use a join, but I've never used them before.

I modified it to this:

$query = "SELECT u.uid, u.parentid AS parentID, u.username, u.userlevel, parent.username AS parentname FROM ".PFX."users AS u JOIN ".PFX."users AS parent ON parent.uid=u.parentid";

Thanks again!