simple select?
396476Aug 29 2007 — edited Aug 29 2007Hi,
I've been attempting to select (for example) the people of which all projects are older than 1 year and of which all projects also have an inactive state (state 3 and 4). I'm pretty sure this can be done in one select, but how?????? Can someone assist?
CREATE TABLE PROJECT (
id_project NUMBER(1),
person VARCHAR2(10),
id_state NUMBER(1),
last_update DATE
);
INSERT INTO PROJECT VALUES (1, 'Joe', 3, TO_DATE('08-08-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (2, 'Mark', 2, TO_DATE('07-05-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (3, 'Mark', 3, TO_DATE('06-27-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (4, 'Mark', 4, TO_DATE('03-14-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (5, 'Carl', 3, TO_DATE('12-08-2004','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (6, 'Carl', 4, TO_DATE('01-22-2005','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (7, 'Sam', 2, TO_DATE('06-15-2006','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (8, 'Paul', 1, TO_DATE('07-30-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (9, 'Paul', 2, TO_DATE('06-02-2006','MM-DD-YYYY'));
So in this case the select should only return the name Carl as both his projects are inactive and both are older than one year.
All I got this far is a statement which retrieves the people of which all projects are older than one year. How do I extend this so it will limit the result to the people of which all projects are also inactive (state 3 and 4)? Being only Carl, as Sams project is still active.
SQL> SELECT person, COUNT(id_project), MAX(last_update)
2 FROM PROJECT
3 GROUP BY person
4 HAVING MAX(last_update) < ADD_MONTHS(SYSDATE,-12)
5 ORDER BY 1;
PERSON COUNT(ID_PROJECT) MAX(LAST_
---------- --------------------------------- -----------------
Carl 2 22-JAN-05
Sam 1 15-JUN-06