How to query exclusion? SQL Help needed

Kyudos

I'm only a very occasional SQL user, so I'm not sure if this is even possible - but if it is, can someone help me with the SQL? The situation: In Table 1, I have a list of components, each with an ID and a type. In Table 2, I have a mapping between Outlet components and Nozzle components (two columns with component IDs in both, lets call them Out_IDs and Nozz_IDs, an Outlet may be linked to many nozzles). I'm trying to find all the Nozzle components that are not linked to any outlets. So in pseudo-SQL: SELECT * FROM Components WHERE (Component.[Type] = Nozzle) AND (Component.[ID] Is Not One Of Mapping.[Nozz_IDs]) It's the AND... bit I can't figure out, probably some sort of JOIN I can't fathom. Thanks all, Dan

Kyudos

Bah! :-D Of course I figure it out as soon as I ask for help!

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

Mycroft Holmes

Ain't is the way. Just some advice. Notice that you need [] around column names with spaces and special characters, eliminate these so [Component Key] becomes ComponentKey, life will be much more pleasant. Also NEVER use key words in a column name (Key and Database) I know these will not be key words when concatenated but it still applies.

Never underestimate the power of human stupidity RAH

Kyudos

Thanks for the advice, I'll bear it in mind for the future. Unfortunately, I've inherited this MDB structure based on an old Fortran record structure so changing the names is not so easy!

Kyudos

Can you see anything wrong with this?

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();
}
}

I can see it gets the right records, it doesn't give anY errors or exceptions, but it also doesn't delete the records! Must be missing something...

Kyudos

The MessageBoard fairies strike again! If I change the SQL to the much simpler:

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

it all works.

Mycroft Holmes

Without going into the code the first point I would make is that deleting a record while inside a collection changes the collection. So you move to the first record and delete it, you collection hanged and where am I supposed to move from. Try starting from the last record in the collection, delete that and move forward 1 record, or go to the last record in the collection. Basically change the way your loop works.

Never underestimate the power of human stupidity RAH

Kyudos

DaoRecordsets don't work like that - presumably for the very reason you describe! 'Delete' simply marks a record as deleted and doesn't alter the record order. this also allows you to rollback changes if necessary. It's all working now though, so thanks for your input!