1

I have two tables named LOGIN and LOGIN_LOG. In LOGIN_LOG table, datetime of every login of every user is saved. in both tables EMP_CODE is common column. I have to pick All usernames, locations from login table and their "last login time" from LOGIN_LOG table. I have tried something like this but not working. please help.

SELECT LOGIN."NAME", LOGIN.LOCATION, LOGIN.WORKING_STATUS, LV.LOGINDATETIME FROM LOGIN LEFT OUTER JOIN (SELECT LOGIN_LOG.EMP_CODE, LOGIN_LOG.LOGINDATETIME FROM LOGIN_LOG ORDER BY LOGIN_LOG.LOGINDATETIME DESC ) AS LV ON LOGIN.EMP_CODE=LV.EMP_CODE WHERE LOGIN.USER_TYPE='NSA';
Improve this question
4
  • it gives error- ORA-00905: missing keyword Commented Jul 8, 2014 at 7:34
  • @mareckmareck: absolutely not. Columns (identifiers) are quoted using double quotes. Single quotes are for character literals. Commented Jul 8, 2014 at 7:47
  • It's not single quoute, it's a back qoute. But actualy you are right, in Oracle you use double quoutes. Commented Jul 8, 2014 at 7:49
  • 1
    @mareckmareck: a backtick is just as wrong as a single quote. Standard SQL (and Oracle) uses double quotes for identifiers. Commented Jul 8, 2014 at 7:57

2 Answers 2

Reset to default
1

Oracle does not support using AS for a table alias.

Plus, you query doesn't make sense. There is no need for the derived table for the outer and an order by in a derived table or sub-query does not make any sense at all.

So it should be something like this:

SELECT login.name, login.location, login.working_status, lv.logindatetime FROM login LEFT OUTER JOIN login_log lv ON login.emp_code = lv.emp_code WHERE login.user_type = 'NSA'

Edit: I overlooked your requirement to get the last login:

SELECT login.name, login.location, login.working_status, lv.logindatetime FROM login LEFT JOIN ( select emp_code, max(logindatetime) as logindatetime from login_log group by emp_code ) lv ON login.emp_code = lv.emp_code WHERE login.user_type = 'NSA'
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

not working. if login_log.logindatetime is lv then how this column is possible lv.emp_code. the error is ORA-00942: table or view does not exist
@user3807573: Sorry I just copied your code. Overlooked the error you made. Corrected now
i already did it but it is giving all logindateTime but i have to choose only last login date time of every user as described in my question.
@user3807573: see my edit. I overlooked your requirement to get the latest login
0

The query above is a bit confusing. In my opinion, the subquery is wrong. You cannot rely on any ordering of the results after a join. In other words, if u have a table A and a table B, both sorted and you make a join, you cannot be sure that the joined result is sorted as well.

I would suggest (without knowing what "does not work" means in your case):

SELECT L."NAME", L.LOCATION, L. WORKING_STATUS, LG.LOGINDATETIME FROM LOGIN L JOIN LOGIN_LOG LG on L.EMP_CODE=LG.EMP_CODE WHERE L.USER_TYPE='NSA' ORDER BY LG.LOGINDATETIME DESC

Always prefer joins over subqueries if possible, since the dbms can handle them better (with optimization, index, etc.)

EDIT:

of course, the LEFT OUTER JOIN is not needed here and could produce an overhead at the query executor so better use JOIN instead.

EDIT2:

I think now I got the problem. You need to use a GROUP BY instead of a ORDER BY:

SELECT L."NAME", L.LOCATION, L. WORKING_STATUS, MAX(LG.LOGINDATETIME) AS LOGINDATETIME FROM LOGIN L JOIN LOGIN_LOG LG on L.EMP_CODE=LG.EMP_CODE WHERE L.USER_TYPE='NSA' GROUP BY L."NAME", L.LOCATION, L.WORKING_STATUS
Improve this answer

3 Comments

i already did it but it is giving all logindateTime but i have to choose only last login date time of every user as described in my question
so you need for each user the datetime of last login?
yes. actually in LOGIN_LOG table there is a record for the date time of each login of every user. I have to choose the last one.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.