left join

seby

i have this query which works fine except its not joining the user table. i know for a fact that the operator: user.userid=pmtext.userid is true.

no error, just cant get access to the user table.. any ideas?

$data->query("
SELECT pmtext.pmtextid, pmtext.title, pmtext.fromusername, COUNT(fromusername) AS fromusertotal
FROM pmtext AS pmtext
LEFT JOIN pm AS pm USING(pmtextid)
LEFT JOIN user ON(user.userid=pmtext.userid)
WHERE pm.userid = '". $bbuserinfo['userid'] ."' AND pm.messageread = 0
GROUP BY pmtext.fromusername ORDER BY dateline DESC
LIMIT 0,10
");

IJoeR


$data->query(" 
SELECT pmtext.pmtextid, pmtext.title, pmtext.fromusername, COUNT(fromusername) AS fromusertotal 
FROM pmtext AS pmtext 
LEFT JOIN pm AS pm USING(pmtextid) 
LEFT JOIN user ON(user.userid=pmtext.userid) 
WHERE pm.userid = '". $bbuserinfo['userid'] ."' AND pm.messageread = 0 
GROUP BY pmtext.fromusername ORDER BY dateline DESC 
LIMIT 0,10 
");

In your select statement do you see these two lines?

FROM pmtext AS pmtext 
LEFT JOIN pm AS pm

From an example i used they rename the variable to something else. Perhaps thats why its not working right. So, it would go something like this:

FROM pmtext AS pm_TEXT 
LEFT JOIN pm AS PM_1

Heres an example of my select statement:


SELECT ij_cat.cat, ij_cat.subcat, ij_gallery2.id, ij_gallery2.thumb, ij_gallery2.name, ij_gallery2.width, ij_gallery2.height, ij_gallery2.flashid, ij_gallery2.gallery_text, ij_gallery2.sid, ij_gallery2.type, ij_gallery2.gid AS GalleryID FROM ij_category LEFT JOIN ij_gallery2 ON gid = ij_category.catid WHERE ij_gallery2.id = $id

I think when you use "AS" you need to rename the variable. I named mine from ij_gallery.gid to 'GalleryID'.

Let me know if that helps or if i am off the mark.

I'm new with the Joins myself.

ahundiak

What data are you trying to get from the user table? Are you sure the fields are listed in the first part of the select statement? And all three tables have a userid?

seby

all three tables contain the user's id, except the column is called 'fromuserid' in the other tables and 'userid' on the user table.

i am trying to access the group the user belongs to on the usertable.

i'm really lost as to why it doens't join the usertable.

seby

well, i re-read you're post ahundiak. turns out i had to add user.usergroupid to the select! as you mentioned.

i never had to do that before, usually if you left join it select all columns for that join.

thanx.