How to use Left Join and ORDER BY in MySQL?

Question

I have two tables: Users and Logs, i use this query to get user info from table 'Users' and user's last access log from table 'Logs'
SELECT users.*,logs.* FROM users LEFT JOIN logs on users.id=logs.user_id GROUP BY logs.user_id

The problem is there are several log records in table 'Logs' for some users so mysql graps the first log record for user (Older Record) but i need mysql to grab the last log record for each user so i can get the last active date!
Any idea?

Answer 1

first off, i have no idea how this query could possibly work. you cannot use a group by clause when you are selecting *, so it's hard to answer your question, when you don't post a properly written query to start. if MySQL somehow allows this, then that is a problem as it violates standard SQL syntax.

my first advise, of course, would be to say, never use * in anything but a simple one table select, and never in production.

second, your query (without the group by clause) [should] return all records from both tables. without really knowing what you are trying to accomplish i can venture a guess. you want the "logs" for a "user"?

if this is the case, you don't need to join the user table at all. there is a User_ID column in the logs table that you can use to find your data. just write a select against the logs table alone.

Answer 2

*sigh*

You say one thing but your data says something else.

What you got there is a One to Many (1:M) relationship from User to Log.

However, you *thought* it was a 1:1 relationship. And you found out that it wasn't, and you can't think your way around it.

The problem here is, you're approaching it the wrong way. You need to filter the data, THEN the query will work. Because you LEFT OUT the requirement from the query that you only want "last active record".

A "hint" for you would be use two queries. First, use a query (perhaps, log date or log timestamp?) to find "only the last active record" for each user.

THEN join the result to the user table for your real answer.

Answer 3

sq. is an ANSI popular it is executed the comparable way although if on oracle, mysql, Ms sequel, DB2, portsqresql etc.. as long by fact the version of your database makes use of the comparable popular. choose * from t1 outer connect t2 on t1.identity = t2.identity

Answer 4

Aria here is your link

http://www.google.co.uk/search?hl=en&q=sample+code+for+How+to+use+Left+Join+and+ORDER+BY+in+MySQL&meta=

http://www.google.co.uk/search?hl=en&q=How+to+use+Left+Join+and+ORDER+BY+in+MySQL&btnG=Search&meta=

table_references:
table_reference, table_reference
| table_reference [INNER | CROSS] JOIN table_reference [join_condition]
| table_reference STRAIGHT_JOIN table_reference
| table_reference LEFT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
| { OJ table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
| table_reference RIGHT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

table_reference:
tbl_name [[AS] alias] [index_hint)]

join_condition:
ON conditional_expr
| USING (column_list)

index_hint:
USE {INDEX|KEY} (index_list)]
| IGNORE {INDEX|KEY} (index_list)]
| FORCE {INDEX|KEY} (index_list)]

index_list:
index_name [, index_name] ...