934281 wrote:post CREATE TABLE statements for both tables.
I have a ‘Data’ table in relation with a ‘Note’ table with a date and I would like to find all the rows in the ‘Data’ table that do not have any ‘Note’ taken in the the last 3 months (including no ‘Note’ at all)?
Hiredate is acting as the 'date the note was taken' so the inner query gets the list of 'IDs' (deptno) that DO HAVE 'notes' taken in the last 90 days.
select * from dept d where deptno not in ( select distinct deptno from emp where hiredate > sysdate - 90 )
934281 wrote:They can usually be used to get the same results. Sometimes, it's easier to understand the logic of one rather than the other. Use whichever one you prefer. I can't guarantee that the optimizer won't do exactly the same thing, regardless of which one you use.
Thanks for the great and quick answers!
Could someone tell me what is the difference between the 2 methodes (Not Exist vs Not In)?
Also, I would I add in the Select the Date of the most recent Note and sort ASC so that I could have the Data that had not activity in the last 3 months and sorted by the one that had no activity the longest time ago first?In that case, I suggest a join, rather than either EXISTS or IN:
If you'd care to post some sample data (CREATE TABLE and INSERT statements), and the results you want from that data, then I could test this.
WITH got_r_num AS ( SELECT d.* , n.notedate , ROW_NUMBER () OVER ( PARTITION BY d.id ORDER BY n.notedate DESC NULLS LAST ) AS r_num FROM data d LEFT OUTER JOIN note n ON n.dataid = d.id ) SELECT * -- Or list all couumns except r_num FROM got_r_num WHERE r_num = 1 AND NVL ( notedate , SYSDATE - 99 -- or any date more than 3 months ago ) < ADD_MONTHS ( SYSDATE , -3 ) ORDER BY notedate ;
934281 wrote:It's not clear at all.
Please let me know if my request is not clear enough.
From what I can make of your requirement, PK value 1 should also be in the expected resultset.
SQL> with data as ( 2 select 1 pk, 'aaa' name from dual union 3 select 2, 'bbb' from dual union 4 select 3, 'ccc' from dual 5 ) 6 , note as ( 7 select 1 fk, 'zzz' note, to_date('15-01-12', 'dd-mm-yy') dt from dual union 8 select 1, 'yyy', to_date('15-02-12', 'dd-mm-yy') from dual union 9 select 1, 'xxx', to_date('15-03-12', 'dd-mm-yy') from dual union 10 select 2, 'rrr', to_date('15-11-11', 'dd-mm-yy') from dual union 11 select 2, 'vvv', to_date('15-12-11', 'dd-mm-yy') from dual union 12 select 2, 'uuu', to_date('15-01-12', 'dd-mm-yy') from dual union 13 select 3, 'ttt', to_date('15-09-11', 'dd-mm-yy') from dual union 14 select 3, 'sss', to_date('15-10-11', 'dd-mm-yy') from dual 15 ) 16 -- 17 -- actual query: 18 -- 19 select pk 20 , name 21 , note 22 , dt 23 from ( select d.pk 24 , d.name 25 , n.note 26 , n.dt 27 , row_number() over (partition by d.pk order by n.dt desc) rn 28 from data d 29 , note n 30 where d.pk = n.fk 31 and dt < trunc(sysdate-90) 32 ) 33 where rn=1 34 order by dt; PK NAM NOT DT ---------- --- --- ------------------- 3 ccc sss 15-10-2011 00:00:00 2 bbb uuu 15-01-2012 00:00:00 1 aaa yyy 15-02-2012 00:00:00
SELECT NODOSSIER, REPRESENTANTNOCERTIFICAT, DETAIL, DATESUIVI FROM (SELECT E.NODOSSIER, E.REPRESENTANTNOCERTIFICAT, S.DETAIL, S.DATESUIVI, Row_number () over ( PARTITION BY E.NODOSSIER ORDER BY S.DATESUIVI DESC ) AS RomNum FROM ENQUETE E, SUIVI S WHERE E.NODOSSIER = S.NODOSSIER AND DATESUIVI < Add_months(SYSDATE, -3)) WHERE ROWNUM = 1 ORDER BY DATESUIVI