What database are you using?
This will work for mssql, but I'm not sure about others.
<cfquery name="Test" datasource="YourDataSource">
select *
from table2
where [idfield] not in (select distinct [idfield]
from table1)
</cfquery>
That is if I understand your table layout. If this doesn't work, give us an
example of a couple of rows of each table.
Steve
-----Original Message-----
From: Merritt Chapman [mailto:[EMAIL PROTECTED]
Sent: Friday, February 20, 2004 4:15 PM
To: CF-Talk
Subject: A tough query... Can CFML help?
I'm wondering if anyone can help me accomplish the following in one SQL
query...
I have two tables. Table1 contains N fields, each containing an ID number.
Table2 contains N fields, each also contain an ID. The trick is that the
IDs in Table2 are 'exclusions' from Table1 and should be used to denote
which IDs are not to be returned in a query of Table1.
For example, if Table1 contains the following IDs:
1|2|3|4|5
And Table2 contains the following exclusions:
2|3
Then a query of the allowed IDs in Table1 should only return:
1|4|5
I'm experimenting with different SQL functions. Is there a CF function that
might help out here that anyone can think of?
Thanks in advance!
Regards,
MC
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
