Ahh, good point. I know I've done this before and I did something along the lines of grabbing the IDs in the subquery for all distinct serialnumbers and then in the outer query grab the serial numbers whose ids don't match the id list from the subquery. If someone wants to use that logic, they can figure out the syntax or I can try to dig up the code I used to do it.
John Burns Certified Advanced ColdFusion MX Developer Wyle Laboratories, Inc. | Web Developer -----Original Message----- From: Tanguy Rademakers [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 12:19 PM To: CF-Talk Subject: Re: find identical records >You can also do > >Select serialnumber >From inventory >Where serialnumber not in(select distinct serialnumber from inventory) that will give you 0 records every time - the subquery "select distinct serialnumber from inventory" returns ALL the serialnumbers used, so you won't find any serialnumbers (in that table) that aren't in that set. /t ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:219257 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
