Oracle table joins

[Ratzie]

I am at a loss.
I need to list the users that have not logged in in 180 days.
I would like to print there manager as well, but manager is also in the same table.


SELECT USER.LOGON_ID, LOGON_SESSION.logged_off, m.LOGON_ID "Manager"
FROM USER, LOGON_SESSION, USER m, USER_ROLE
where USER.user_id = LOGON_SESSION.for_user
AND m.user_id = USER_ROLE.FOR_USER
AND USER_ROLE.ROLE = '2'
AND LOGON_SESSION.logged_off < sysdate -180
(this does not work it returns wrong data)
*************

2 is the Supervisor roll.

If I split into two separate sql, I can list all users that are managers.
select * from USER, USER_ROLE
where USER.user_id = USER_ROLE.FOR_USER
and USER_ROLE.ROLE = '2';

Then I can just get the users that have not logged in in 180 days.
SELECT USER.logon_id, LOGON_SESSION.logged_off
FROM USER, LOGON_SESSION
where USER.user_id = LOGON_SESSION.for_user
AND LOGON_SESSION.logged_off < sysdate -180

But how can I put both together...

[Frank de Vries]

The way you layed-out your question it is hard to puzzle out as we can not reproduce it.

Here is an example on how to ask sql questions,
this is prob also a better forum for asking these type of Q.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4725668677832

good luck

[Ratzie]

appreciate your help