xero.03 Posted October 15, 2006 Share Posted October 15, 2006 [code]SELECT @frm_id:=b.fid as forum_id, a.fid as cat_id, b.ftitle as forum_title, b.fdesc as forum_desc, b.fmodid as forum_mods, c.poid as lp_id, c.ponode as lp_node, c.potitle as lp_title, d.uid as lp_posterid, d.username as lp_poster, c.podate as lp_postdate FROM pre_forums a INNER JOIN pre_forums b ON (a.fid=b.fparent AND a.fparent=0) INNER JOIN pre_posts c ON (c.poforum=b.fid AND c.podate=(SELECT MAX(podate) FROM pre_posts WHERE poforum=@frm_id)) INNER JOIN pre_users d ON d.uid=c.poposter[/code]Basically the variable declared in the beginning isn't being passed on to my sub-query in "pre_posts c", is there another way to pass that value or should I just call the last post data with each forum (in that case for each forum my query number will increase creating slightly more overhead, which chouldn't be too bad but I'm looking to avoid that)EDIT: After alittle consideration it may be better to use a left join (since the forum might not always have posts) but either way my original request is still the same. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 15, 2006 Share Posted October 15, 2006 The problem is using a variable in the same "statement" -- you tend to get rather funny results. Quote Link to comment Share on other sites More sharing options...
xero.03 Posted October 15, 2006 Author Share Posted October 15, 2006 I'm running it though PHP, hmmm so I should just call the last post query seperate since there's no way to do it without a set statement? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 15, 2006 Share Posted October 15, 2006 You could use a SET statment... Quote Link to comment Share on other sites More sharing options...
xero.03 Posted October 17, 2006 Author Share Posted October 17, 2006 Examples? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 17, 2006 Share Posted October 17, 2006 Sorry, I didn't read your first response carefully the first time... why are you using a variable at all here? Quote Link to comment Share on other sites More sharing options...
xero.03 Posted October 17, 2006 Author Share Posted October 17, 2006 the join between the last_post data relies on the current fid (forum id) but since the call is being made in a subquery I can't just use the table name and instead am trying to use a variable but the variable doesn't seem to want to work in the subquery Quote Link to comment Share on other sites More sharing options...
fenway Posted October 17, 2006 Share Posted October 17, 2006 Are you sure that you can't reference the original field directly? There shouldn't be a problem, as far as I can see. Quote Link to comment Share on other sites More sharing options...
xero.03 Posted October 20, 2006 Author Share Posted October 20, 2006 Nevermind (for the most part) I found a superior solution: preorder tree transversal. Now it's just up to messing around with it awhile and finding some good tutorials, thanks anyways fenway (unless you can teach me non-binary tree transversal ;) ). Quote Link to comment Share on other sites More sharing options...
fenway Posted October 20, 2006 Share Posted October 20, 2006 You mean for storing hierarchal data? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.