Re: Find no records
Thanks for your help everyone, definitely got it working now. Thanks, Saturday On 2 Nov 2005, at 22:54, Brian Peddle wrote: > SELECT > u.userID, > u.firstName, > u.lastName, > u.emailAddress, > u.password > FROM > tbl_020publicUsers u > WHERE > not exists (select * from tbl_020eventDetails e where u.userID = > e.userID) > > > May perform a bit better than NOT IN > > > -Original Message- > From: Charlie Griefer [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 02, 2005 4:56 PM > To: CF-Talk > Subject: Re: Find no records > > SELECT > u.userID, > u.firstName, > u.lastName, > u.emailAddress, > u.password > FROM > tbl_020publicUsers u > WHERE > u.userID NOT IN (SELECT DISTINCT userID FROM tbl_020eventDetails) > > > On 11/2/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote: >> Hi guys, >> >> Since getting help on my last problem which was finding records of >> users (from user table) which have events in the events table I am >> now trying to list all the users in the users table which don't have >> any rows which match in the events table. >> >> >> SELECT >>u.userID, >>u.firstName, >>u.lastName, >>u.emailAddress, >>u.password, >>e.userID >> FROM >>tbl_020publicUsers u INNER JOIN tbl_020eventDetails e >>ON u.userID <> e.userID >> >> >> I thought something like the above might work but doesn't seem to. >> >> Any help would be great, thanks. >> >> Saturday >> >> >> >> >> > > > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222985 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Find no records
SELECT u.userID, u.firstName, u.lastName, u.emailAddress, u.password FROM tbl_020publicUsers u WHERE not exists (select * from tbl_020eventDetails e where u.userID = e.userID) May perform a bit better than NOT IN -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 4:56 PM To: CF-Talk Subject: Re: Find no records SELECT u.userID, u.firstName, u.lastName, u.emailAddress, u.password FROM tbl_020publicUsers u WHERE u.userID NOT IN (SELECT DISTINCT userID FROM tbl_020eventDetails) On 11/2/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote: > Hi guys, > > Since getting help on my last problem which was finding records of > users (from user table) which have events in the events table I am > now trying to list all the users in the users table which don't have > any rows which match in the events table. > > > SELECT >u.userID, >u.firstName, >u.lastName, >u.emailAddress, >u.password, >e.userID > FROM >tbl_020publicUsers u INNER JOIN tbl_020eventDetails e >ON u.userID <> e.userID > > > I thought something like the above might work but doesn't seem to. > > Any help would be great, thanks. > > Saturday > > > > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222984 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Find no records
Hi guys, Since getting help on my last problem which was finding records of users (from user table) which have events in the events table I am now trying to list all the users in the users table which don't have any rows which match in the events table. SELECT u.userID, u.firstName, u.lastName, u.emailAddress, u.password, e.userID FROM tbl_020publicUsers u INNER JOIN tbl_020eventDetails e ON u.userID <> e.userID I thought something like the above might work but doesn't seem to. Any help would be great, thanks. Saturday I suspect the other suggestions would work, but since we originally went the inner join rout why not do the outer join route which is designed for this purpose. SELECT u.userID, u.firstName, u.lastName, u.emailAddress, u.password, e.userID FROM tbl_020publicUsers u LEFT OUTER JOIN tbl_020eventDetails e ON u.userID = e.userID AND e.userID IS NULL -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222983 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Find no records
Maybe this will work. I may not be understanding what you need. SELECT u.userID, u.firstName, u.lastName, u.emailAddress, u.password, e.userID FROM tbl_020publicUsers u WHERE u.userID NOT IN (SELECT UserID FROM tbl_020eventDetails e) On 11/2/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote: > > Hi guys, > > Since getting help on my last problem which was finding records of > users (from user table) which have events in the events table I am > now trying to list all the users in the users table which don't have > any rows which match in the events table. > > > SELECT > u.userID, > u.firstName, > u.lastName, > u.emailAddress, > u.password, > e.userID > FROM > tbl_020publicUsers u INNER JOIN tbl_020eventDetails e > ON u.userID <> e.userID > > > I thought something like the above might work but doesn't seem to. > > Any help would be great, thanks. > > Saturday > > > > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222979 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Find no records
Try this code. SELECT u.userID,u.firstName,u.lastName,u.emailAddress,u.password FROM tbl_020publicUsers u WHERE (SELECT count(*) FROM tbl_020eventDetails e WHERE e.userID = u.userID) = 0 Charlie Hanlon Web Apps Developer Food Service Enablers, Inc. www.fsenablers.com The People. The Platform. The Products. Make the Difference - Original Message - From: "Saturday (Stuart Kidd)" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Wednesday, November 02, 2005 4:50 PM Subject: Find no records > Hi guys, > > Since getting help on my last problem which was finding records of > users (from user table) which have events in the events table I am > now trying to list all the users in the users table which don't have > any rows which match in the events table. > > > SELECT > u.userID, > u.firstName, > u.lastName, > u.emailAddress, > u.password, > e.userID > FROM > tbl_020publicUsers u INNER JOIN tbl_020eventDetails e > ON u.userID <> e.userID > > > I thought something like the above might work but doesn't seem to. > > Any help would be great, thanks. > > Saturday > > > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222978 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Find no records
> Since getting help on my last problem which was finding > records of users (from user table) which have events in > the events table I am now trying to list all the users > in the users table which don't have any rows which match > in the events table. > > > SELECT > u.userID, > u.firstName, > u.lastName, > u.emailAddress, > u.password, > e.userID > FROM > tbl_020publicUsers u INNER JOIN tbl_020eventDetails e > ON u.userID <> e.userID > SELECT columns FROM tbl_020publicUsers u WHERE u.userID NOT IN (SELECT e.userID FROM tbl_020eventDetails e) -Justin ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222977 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Find no records
SELECT u.userID, u.firstName, u.lastName, u.emailAddress, u.password FROM tbl_020publicUsers u WHERE u.userID NOT IN (SELECT DISTINCT userID FROM tbl_020eventDetails) On 11/2/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote: > Hi guys, > > Since getting help on my last problem which was finding records of > users (from user table) which have events in the events table I am > now trying to list all the users in the users table which don't have > any rows which match in the events table. > > > SELECT >u.userID, >u.firstName, >u.lastName, >u.emailAddress, >u.password, >e.userID > FROM >tbl_020publicUsers u INNER JOIN tbl_020eventDetails e >ON u.userID <> e.userID > > > I thought something like the above might work but doesn't seem to. > > Any help would be great, thanks. > > Saturday > > > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222976 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
