CODEWITHSUNDEEP
sqloracle-databasejoin
Vivek Mishra
Vivek Mishra

Reputation: 1804

oracle query is not working

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';

Upvotes: 1

Views: 307

Answers (2)

paubo147
paubo147

Reputation: 778

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

Upvotes: 0

user330315
user330315

Reputation:

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'

Upvotes: 1

Related Questions