homepage Welcome to WebmasterWorld Guest from 54.145.182.50
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Join statment WITH Where and Order By clauses
neophyte

10+ Year Member



 
Msg#: 4449196 posted 7:06 am on May 4, 2012 (gmt 0)

Hello All -

I'm trying to join all of the columns in one table (tbl_admin_users) with 2 fields in another table(tbl_access_log) using both a WHERE and ORDER BY clause on tbl_access_log.

So far I can't get my sql statement (shown below) to work.

$sql =
"SELECT *
FROM tbl_admin_users
LEFT JOIN tbl_access_log.fld_login, tbl_access_log.fld_logout
WHERE tbl_access_log.fld_user_id = '" . $adminId . "'
ORDER by tbl_access_log.fld_logout DESC limit 1";

What's going on is that there are multiple rows in tbl_access_log with the same foreign key...

(WHERE tbl_access_log.fld_user_id = '" . $adminId . "')

and what I need to get is a row matching $adminId which also contains the most recent unix timestamp which is found in the fld_logout...

(ORDER by tbl_access_log.fld_logout DESC limit 1)

Can someone show me where I'm going wrong with my sql statement?

All replies greatly appreciated!

 

noyearzero

5+ Year Member



 
Msg#: 4449196 posted 3:02 pm on Jun 19, 2012 (gmt 0)

I'm not sure if this is exactly right....but it should put you on the path. (replace `tbl_access_log.id` with the actual primary index for the table `tbl_access_log`)

SELECT
tbl_access_log.fld_login,
tbl_access_log.fld_logout,
tbl_admin_user.*
FROM tbl_admin_users
LEFT JOIN tbl_access_log ON tbl_access_log.id = (
SELECT
tbl_access_log.id
FROM tbl_access_log
WHERE
tbl_access_log.fld_user_id = '" . $adminId . "'
ORDER BY
tbl_access_log.fld_logout DESC
LIMIT 1
)

Additionally, if your $adminId variable actually refers to the current row in the outer table (tbl_admin_users) you could replace

'" . $adminId . "'

with

tbl_admin_users.fld_user_id

(assuming fld_user_id is the name of the same field)

That would be more appropriate if your outer table is meant to pull back more than 1 row

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4449196 posted 4:20 pm on Jun 19, 2012 (gmt 0)

I always get this wrong off the top of my head and have to mantra, it goes in this order:

select/from/join/where/order/limit

I'm guessing at a few of the field names.

$sql =
"select tbl_admin_users.username, tbl_access_log.login,tbl_access_log.logout
from tbl_admin_users
left join tbl_access_log on tbl_access_log.fld_user_id = tbl_admin_users.fld_user_id
where tbl_access_log.fld_user_id = '" . $adminId . "'
order by tbl_access_log.fld_logout desc limit 1";

Some notes:

- Do not use * unless you specifically need all fields, it's a lot of overhead that will cost you on complex multi table joins.
- If admin ID is numeric, which it should be, it doesn't need to be quoted (and shouldn't.)

where tbl_access_log.fld_user_id = $adminId
If it's just a single table select, and you're sure it always will be (i.e., no other potential joins in the near future) you can move the "joining" to the where clause:

$sql =
"select tbl_admin_users.username, tbl_access_log.login, tbl_access_log.logout
from tbl_admin_users,tbl_access_log
where tbl_access_log.fld_user_id = $adminId
and tbl_access_log.fld_user_id = tbl_admin_users.fld_user_id
order by tbl_access_log.fld_logout desc limit 1";

neophyte

10+ Year Member



 
Msg#: 4449196 posted 12:27 am on Jun 20, 2012 (gmt 0)

Noyearzero and Rockinbil -

Thanks to you both for your input on this - I was struggling for so long on this that I just settled for doing a multiple query routine that - while it works - is really inefficient. Now I can get back to doing it the right way.

Rockinbil - thanks also for the Mantra... I'm sure this will help others as well.

noyearzero

5+ Year Member



 
Msg#: 4449196 posted 6:05 pm on Jun 21, 2012 (gmt 0)

rocknbil:

Is there any speed advantage to 'move the "joining" to the where clause' as opposed to what i suggested? I always thought it was faster to filter out as many rows from the join table in the ON clause rather than in the FROM table's WHERE? But to be honest i could be completely wrong.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4449196 posted 3:33 pm on Jun 22, 2012 (gmt 0)

I don't know - bench test it. :-) My **guess** would be that your approach contains a nested select, and may traverse more rows because it's essentially two selects. The approach I suggested is certainly a little less complex in the coding part.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved