How to select same column twice with different condition?

[Umesh Anant]

Hi,

I have a table which have three fields.

The three columns are:
username lastname Timestamp

Now I have to fetch all the entries for username separately which have lastname as 'Hello' and timestamp for last six months. I need both the data in separate rows.

Thanks,
Umesh

[amitpatel66]

Can you post what you have tried so far?

[Umesh Anant]

select password from table1 where user_id = ? AND password in (SELECT password FROM table1 where user_id = ? AND TO_CHAR(password_set_date, 'dd-mon-yyyy hh24:mi:ss') < TO_CHAR(CURRENT_DATE, 'dd-mon-yyyy hh24:mi:ss') AND TO_CHAR(add_months (sysdate, -6), 'dd-mon-yyyy hh24:mi:ss') >TO_CHAR(password_set_date, 'dd-mon-yyyy hh24:mi:ss'))

I need data for last six times separately and for last six months separately!

[amitpatel66]

Can you try this:

select password from table1 where user_id = ? AND password in (SELECT password FROM table1 where user_id = ? AND TO_CHAR(password_set_date, 'dd-mon-yyyy hh24:mi:ss') < TO_CHAR(CURRENT_DATE, 'dd-mon-yyyy hh24:mi:ss') 
UNION ALL
select password from table1 where
password_set_date BETWEEN ADD_MONTHS(SYSDATE,-6) AND SYSDATE