Database

CREATE FUNCTION dbo.FindRoot(@id int)
RETURNS int
AS  
BEGIN 
  DECLARE @Id_parent int
  SELECT @Id_parent= Id_parent
  FROM TableName
  WHERE id = @id
  WHILE @Id_parent <> NULL
    BEGIN
      SELECT @id = @Id_parent
      SELECT @Id_parent = Id_parent
      FROM PrimeInfo
      WHERE id = @id
    END
  RETURN @id
END

CREATE PROCEDURE BuildTree(@id int)
AS 
SET NOCOUNT ON
CREATE TABLE #results(level int, id int, id_parent int)
DECLARE @id_parent int
DECLARE @level int
SELECT @level = 1
DECLARE @root int
SELECT @root = dbo.FindRoots(@id)
CREATE TABLE #stack (id int, level smallint)
INSERT INTO #stack VALUES (@root, @level)
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
  BEGIN
    SELECT @id = s.id, @id_parent= IsNull(t.id_parent, 0)
    FROM #stack s INNER JOIN TableName t
    ON t.id = s.id
    WHERE level = @level
    INSERT INTO #results VALUES (@level, @id, @id_parent)
    DELETE FROM #stack
    WHERE level = @level
    AND id = @id
    INSERT #stack
    SELECT id, @level + 1
    FROM TableName
    WHERE id_parent = @id
    IF @@ROWCOUNT > 0 
      BEGIN
        SELECT @level = @level + 1
    END
 END--IF EXISTS
 ELSE
 BEGIN
   SELECT @level = @level - 1
 END
END -- WHILE
SELECT id, id_parent, level FROM #results

SELECT * FROM Components LEFT JOIN Nozzles ON Components.[Component Key] = Nozzles.[Nozzle Key]
WHERE (((Components.[Database Number])=15) AND ((Nozzles.[Nozzle Key]) Is Null));

CDaoDatabase* pDB = static_cast<CMainFrame *>(AfxGetMainWnd())->GetDatabase();
CMyRecords Records(pDB);

CString sSQL = "SELECT * FROM Components LEFT JOIN Nozzles ON
Components.[Component Key] = Nozzles.[Nozzle Key] WHERE
(((Components.[Database Number])=15) AND ((Nozzles.[Nozzle Key]) Is Null))";
	
try
{
    Records.Open(dbOpenDynaset, sSQL, 0);

    if (Records.IsBOF() || Records.IsEOF())
    {
        return;
    }

    Records.MoveFirst();
    while (!Records.IsEOF())
    {
        if (Records.CanUpdate())
        {
            Records.Delete();
        }
        Records.MoveNext();
    }

    Records.Close();
}
catch(CDaoException *e)
{
    e->ReportError();
    e->Delete();
    if (Records.IsOpen())
    {
        Records.Close();
    }
}

"SELECT * FROM Components WHERE (Components.[Database Number])=15 AND Components.[Component Key] NOT IN (SELECT [Nozzle Key] FROM Nozzles)"