multiple fields in SELECT WHERE IN

Hi All

I'm in the process of trying to write a query (in access 2000) that copys data between tables, then updates related tables.

The problem:

One of the tables is linked on 2 fields, and I need to do a where not in query, so I can't use a join.

Is it possible to do something along the lines of:

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

Any help much appreciated

Mark MiddlemistYES YOU CAN

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

this works fine, but check that field1 and ref1 should have same datatype etc.,|||This won't be upgradable to sql server though.
You could

select * from table1
where not exists
(select * from table2 where table1.field1 = table2.ref1 and table1.field2 = table2.ref2)

or

select table1.*
from table1 left outer join table2
on table1.field1 = table2.ref1 and table1.field2 = table2.ref2
where table1.field1 is null

these will still bring back results if the fields allow null whereas the in clause may well not.
In all cases the results may not be correct for nullable fields.

Multiple cascade paths

Hello,

I started using SQL Server 2005 and encountered a bug/missing feature.

The cascade behavior on updates and deletions is made in such a way 2 different fields in a given table cannot be accessed through 2 different cascade paths. This limitation is "explained", in fact, in the documentation where the engine enforces the reachable-on-update/delete tables to fit in a tree. A directed acyclic graph would have been appreciated. Or, if you are bound to using a tree, why not replacing table nodes by field ones.

That limitation raises implementation-dependent concerns up to the design phase. Whereas MS Access 2003 allows such behavior... couldn't you collaborate with the Access development team so you did not have to redo the job?

Finally, browsing other threads, I came across another similar limitation which is as restrictive as the previous one, according to me. It's impossible to self-reference a table... and Access supports this feature too.
I thought you were ready after 5 years, but I see very few enhancements in compliance with the SQL standard, not to say any. Besides that, SQL Server 2005 remains a nice tool to use.

Yours,
Intenion

The limitation is still there in June 2007 CTP of SQL Server 2008!!!!

Seems like a bad joke since Oracle and DB2 and probably all other databases has supported this for year (even decades?).

Makes all these nice features like "Delete Cascade" and "Delete Set Null" worthless in most real world relation databases.

Really hope they will fix this to the final SQL Server 2008...

/Andreas

|||

(The cascade behavior on updates and deletions is made in such a way 2 different fields in a given table cannot be accessed through 2 different cascade paths. This limitation is "explained", in fact, in the documentation where the engine enforces the reachable-on-update/delete tables to fit in a tree. A directed acyclic graph would have been appreciated. Or, if you are bound to using a tree, why not replacing table nodes by field ones.

That limitation raises implementation-dependent concerns up to the design phase. Whereas MS Access 2003 allows such behavior... couldn't you collaborate with the Access development team so you did not have to redo the job?)

It is not a bug but the correct implementation of ANSI SQL DRI(declarative referential integrity) rules which says if a references b b must exist. What that means primary key a becomes foreign key b you can use a to delete b Cascade delete, you can also use a to update b cascade update and a can set b to default SET Default and null SET NULL.

What is implemented in Access is not relational, I have not used DB2 for a while now but Oracle DRI is not different from SQL Server. Microsoft did not say you cannot have more than one but for that you need a trigger, a trigger can be used to delete and update as many as you want.

BTW that feature came to us from one of the most fabulous Algebras of the 20th century all twenty six pages of it.

Multiple cascade paths

Hello,

I started using SQL Server 2005 and encountered a bug/missing feature.

The cascade behavior on updates and deletions is made in such a way 2 different fields in a given table cannot be accessed through 2 different cascade paths. This limitation is "explained", in fact, in the documentation where the engine enforces the reachable-on-update/delete tables to fit in a tree. A directed acyclic graph would have been appreciated. Or, if you are bound to using a tree, why not replacing table nodes by field ones.

That limitation raises implementation-dependent concerns up to the design phase. Whereas MS Access 2003 allows such behavior... couldn't you collaborate with the Access development team so you did not have to redo the job?

Finally, browsing other threads, I came across another similar limitation which is as restrictive as the previous one, according to me. It's impossible to self-reference a table... and Access supports this feature too.
I thought you were ready after 5 years, but I see very few enhancements in compliance with the SQL standard, not to say any. Besides that, SQL Server 2005 remains a nice tool to use.

Yours,
Intenion

The limitation is still there in June 2007 CTP of SQL Server 2008!!!!

Seems like a bad joke since Oracle and DB2 and probably all other databases has supported this for year (even decades?).

Makes all these nice features like "Delete Cascade" and "Delete Set Null" worthless in most real world relation databases.

Really hope they will fix this to the final SQL Server 2008...

/Andreas

|||

(The cascade behavior on updates and deletions is made in such a way 2 different fields in a given table cannot be accessed through 2 different cascade paths. This limitation is "explained", in fact, in the documentation where the engine enforces the reachable-on-update/delete tables to fit in a tree. A directed acyclic graph would have been appreciated. Or, if you are bound to using a tree, why not replacing table nodes by field ones.

That limitation raises implementation-dependent concerns up to the design phase. Whereas MS Access 2003 allows such behavior... couldn't you collaborate with the Access development team so you did not have to redo the job?)

It is not a bug but the correct implementation of ANSI SQL DRI(declarative referential integrity) rules which says if a references b b must exist. What that means primary key a becomes foreign key b you can use a to delete b Cascade delete, you can also use a to update b cascade update and a can set b to default SET Default and null SET NULL.

What is implemented in Access is not relational, I have not used DB2 for a while now but Oracle DRI is not different from SQL Server. Microsoft did not say you cannot have more than one but for that you need a trigger, a trigger can be used to delete and update as many as you want.

BTW that feature came to us from one of the most fabulous Algebras of the 20th century all twenty six pages of it.