On Dec 27, 2007 11:29 AM, Ed Dressel <Dressel@...> wrote:

> I am looking for all of the master records that have one or no detail
> records.

CREATE DATABASE "/tmp/paso.fdb";
CREATE TABLE MASTER (ID INTEGER);
CREATE TABLE DETAIL (ID INTEGER, MASTER_ID INTEGER);
INSERT INTO MASTER VALUES (0);
INSERT INTO MASTER VALUES (1);
INSERT INTO MASTER VALUES (2);
INSERT INTO MASTER VALUES (3);
INSERT INTO MASTER VALUES (4);
INSERT INTO MASTER VALUES (5);

INSERT INTO DETAIL VALUES (1, 1);
INSERT INTO DETAIL VALUES (2, 2);
INSERT INTO DETAIL VALUES (3, 2);
INSERT INTO DETAIL VALUES (4, 3);
INSERT INTO DETAIL VALUES (5, 3);
INSERT INTO DETAIL VALUES (6, 3);
INSERT INTO DETAIL VALUES (6, 4);
INSERT INTO DETAIL VALUES (6, 4);
INSERT INTO DETAIL VALUES (6, 4);
INSERT INTO DETAIL VALUES (6, 4);

SELECT MASTER.ID, COUNT(DETAIL.ID)
FROM MASTER
LEFT JOIN DETAIL ON DETAIL.MASTER_ID=MASTER.ID
GROUP BY MASTER.ID
HAVING COUNT(DETAIL.ID) BETWEEN 0 AND 1
;



--
Saludos,
Toño
----
http://agc.com.mx/antoniogc/blog