>>>Combined Table has Fields ID,AssemblyId,PartsId >>> >>>Parent Table One has ID,AssemblyId >>> >>>Parent Table Two has ID,PartsId >>>>>>What I tried that returns all records when I want only records that are not in parent tables.
>>>Select * >>>FROM Combined >>>Where exists >>>( >>> SELECT a.* >>> FROM Combined a >>> WHERE somefield<>'2' >>> and NOT EXISTS >>> ( >>> SELECT id >>> FROM ParentTwo b >>> WHERE a.id = b.Id >>> and a.PartsId = b.PartsId >>> and not exists >>> ( >>> SELECT id >>> FROM ParentOne c >>> where a.Id = c.Id and a.AssemblyId = c.AssemblyId >>> ) >>> ) >>>) >>>>>>Any pointers on where I'm screwing up would be welcomed.
>>SELECT c.* FROM Combined c LEFT Join Parent1 p1 on c.ID = P1.ID >>LEFT JOIN Parent2 p2 on c.ID = p2.ID where p1.ID IS NULL or p2.ID IS NULL>
>SELECT Combined.* > FROM Combined >LEFT JOIN (SELECT Id FROM Parent1 > UNION > SELECT Id FROM Parent2) Tbl1 >ON Combined.Id = Tbl1.Id >WHERE Tbl1.Id IS NULL >>
>SELECT Combined.* > FROM Combined >WHERE Combined.Id NOT IN > (SELECT Id FROM Parent1 > UNION > SELECT Id FROM Parent2) >AND means that we don't have this record in Parent1 and in Parent2. I'm not sure I got the exact idea of what was Jeff asking.