SELECT ID's with No Active Records.

[murdock75]

I am running MY SQL 4.0.24.

I have a linking table which has four Columns: ID, pID, cID, and status. I want to get all cID's for which no record exists or no record exists with a status of 1 (there are no active records for the cID).

For instance if the cID is in four records and three have a status of 1 it would not be returned but if the cID is in two records both with a status of 0 it would be returned. If the cID did not exist at all in the table it would be returned.

This SQL is giving me success with returning all cID's that do not exist in the table.

SELECT tblChild.cID FROM tblChild LEFT OUTER JOIN tblParentChild ON tblChild.cID = tblParentChild.cID WHERE tblParentChild.cID IS NULL

I can also use this SQL but it only returns cID's that do not exist in the table
SELECT tblChild.cID, COUNT(tblParentChild.Status) as iCount FROM tblChild LEFT OUTER JOIN tblParentChild ON tblChild.cID = tblParentChild.cID WHERE tblParentChild.Status = 1 GROUP BY tblChild.cID HAVING iCount = 0;

[SURFThru]

Hi Murdock75,

This is how I would code it in SQL Server it should work in MySQL.

Select Parent.CID
From Parent
Where Parent.CID in
(
Select Child.CID
Fom Child
Where Child.CID = Parent.CID
and Status = 0
)
Or Parent.CID not in
(
Select Child.CID
From Chilld
Where Child.CID = Parent.CID
)

The Where returns only where CID <> 0 as you mentioned. The OR will work if there are no rows at all for CID.

Regards,
Patrick

SURFThru.com - Better Search Results (http://www.SURFThru.com)