Sub select if you can use them: SELECT [User], Job FROM table1 WHERE [User] NOT IN (SELECT [User] FROM table2)
Or Joining and excluding nulls in User in table2: SELECT table1.[User], table1.Job FROM table1 LEFT OUTER JOIN table2 ON ( table1.[User] = table2.[User] ) WHERE table2.[User] IS NULL Is that the sort of thing you meant? Ade -----Original Message----- From: Justin To: [EMAIL PROTECTED] Sent: 05/12/03 15:04 Subject: RE: [ cf-dev ] SQL question Trying to do in the DB only, without a cursor, but its Friday. Feel like it should be possible, maybe not! Thanks Justin -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 05 December 2003 14:48 To: [EMAIL PROTECTED] Subject: Re: [ cf-dev ] SQL question actually that AND should be an OR instead. [EMAIL PROTECTED] sign.co.uk To: <[EMAIL PROTECTED]> cc: 05/12/2003 14:46 Subject: Re: [ cf-dev ] SQL question Please respond to dev how's this: <!--- first query ---> <cfquery name="q1"> select User, Job from sometable where somecriteria </cfquery> <cfif q1.recordcount> <!--- 2nd ---> <cfquery> select ID, User, Job from table1 where <cfloop query="q1"> (User <> '#q1.User#' AND Job <> '#q1.Job#') OR </cfloop> 1 = 0 </cfquery> </cfif> Duncan Cumming IT Manager http://www.alienationdesign.co.uk mailto:[EMAIL PROTECTED] Tel: 0141 575 9700 Fax: 0141 575 9600 Creative solutions in a technical world ---------------------------------------------------------------------- Get your domain names online from: http://www.alienationdomains.co.uk Reseller options available! ---------------------------------------------------------------------- ---------------------------------------------------------------------- "Justin" <JustinMacCarthy@ To: "Cfuk" <[EMAIL PROTECTED]> online.ie> cc: Subject: [ cf-dev ] SQL question 05/12/2003 14:26 Please respond to dev If I have table 1 -------------------- user job -------------------- joe manager sam Staff harry manager and a query that returns -------------------- user job -------------------- joe manager harry manager dick staff is there a way to get this (i.e. anyone in table1 that don't have matching rows in the query) in SQL ---------------- User job ---------------- Sam staff Thanks Justin -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
