xerodefect Posted October 18, 2006 Share Posted October 18, 2006 Hello guys here :POk i got 3 tables to grab info. topics, post, and accounts. Well in tables i need to grab 2 diff account rows. Im doing this in one query using inner,left, and right join. Im having trouble grabbing 2 rows from accounts.Query: SELECT t.id AS tid,t.*,p.id AS pid,p.*,a.id AS aid,a.* FROM topics AS t INNER JOIN posts AS p ON t.post_id=p.id INNER JOIN accounts AS a ON t.owner=a.id AND p.post_owner=a.id WHERE t.board_id=$board[bid] ORDER BY t.date DESC LIMIT $start,$limitI really dont know how to match owner (its an id of accounts) and post_owner (id from accounts) to replace with the account username where it matchs. Can you, if soo please help. (I never had to do this b/4 :D) Quote Link to comment Share on other sites More sharing options...
btherl Posted October 18, 2006 Share Posted October 18, 2006 [code]SELECT t.id AS tid,t.*,p.id AS pid,p.*,a.id AS aid,a.*FROM topics AS tINNER JOIN posts AS p ON t.post_id=p.idINNER JOIN accounts AS a ON t.owner=a.id AND p.post_owner=a.idWHERE t.board_id=$board[bid]ORDER BY t.date DESCLIMIT $start,$limit[/code]I've reformatted the query a bit.I don't fully understand what you are trying to do. What do you want your results to look like? Quote Link to comment Share on other sites More sharing options...
xerodefect Posted October 18, 2006 Author Share Posted October 18, 2006 Well now how do i get the 2 account username from the 2 IDs. (t.owner and p.post_owner) something like that i forgot im not looking at it :PthanksEDIT - Snapped pciture. Wanna replace the thing circal with the user's username. (Topics Owner Replies Views Last Action is the order)[attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
btherl Posted October 19, 2006 Share Posted October 19, 2006 Ok I understand now. Instead of the account id, you want to display the account username. But you want to do this for both the owner and the post_owner? To do that in a single query, you'll need to join with the accounts table twice (assuming the accounts table has the username).[code]JOIN accounts AS a ON t.owner = a.idJOIN accounts AS ap ON p.post_owner = ap.id[/code]Then a.username will be the owner's username, and ap.username will be the post_owner's username. Is that what you're looking for?There's no need to specify "INNER", since that's the default. Quote Link to comment Share on other sites More sharing options...
xerodefect Posted October 19, 2006 Author Share Posted October 19, 2006 Hey dude thanks i see what you mean. I never thought of doing it that way.SQL Now: [code]SELECT t.id AS tid,t.*,p.id AS pid,p.*,a.id AS aid,a.username AS ausername,a.*, ap.id AS apid, ap.username AS apusername, ap.* FROM topics AS t INNER JOIN posts AS p ON t.post_id=p.id JOIN accounts AS a ON t.owner = a.idJOIN accounts AS ap ON p.post_owner = ap.id WHERE t.board_id=$board[bid] ORDER BY t.date DESC LIMIT $start,$limit[/code]lol 2 inner joins and rename them :D tyz :P 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.