Select IDs that only have 1 entry
I have a table a bit like this: id whatever1 whatever2 -- 10 - blah1 - blah2 12 - abc1 - abc2 10 - xyz1 - xyz2 15 - pdq1 - pdq2 I'm trying to come up with a query that will only pull the rows that have 1 entry and 1 entry only. None of the ones that have duplicates should be returned. In this example, 12 and 15 will be returned, but 10 won't.I can think of a few awkward ways to do it, but am looking for a simple one.I'm not that familiar with MySQL. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Select IDs that only have 1 entry
I have thought about the same thing.I've also wondered about a NOT DISTINCT command to find records that do have duplicates.Anyone with any bright ideas? John -Original Message- From: Todd Ashworth [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 15, 2004 4:00 PM To: CF-Talk Subject: Select IDs that only have 1 entry I have a table a bit like this: id whatever1 whatever2 -- 10 - blah1 - blah2 12 - abc1 - abc2 10 - xyz1 - xyz2 15 - pdq1 - pdq2 I'm trying to come up with a query that will only pull the rows that have 1 entry and 1 entry only. None of the ones that have duplicates should be returned. In this example, 12 and 15 will be returned, but 10 won't.I can think of a few awkward ways to do it, but am looking for a simple one.I'm not that familiar with MySQL. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Select IDs that only have 1 entry
Nevermind, maybe.These 2 options were posted on the macromia forums. SELECT fields FROM table AS t1 WHERE EXISTS (SELECT 1 FROM table AS t2 WHERE t1.id = t2.id HAVING count(*) = 1) and/or SELECT t1.id, t1.whatever1, t1.whatever2 FROM table AS t1 INNER JOIN table AS t2 ON t1.id = t2.id HAVING COUNT(*) = 1 GROUP BY t1.id, t1.whatever1, t1.whatever2 The first one doesn't work because my version of MySQL doesn't support sub-queries.I'm getting ready to try the second. - Original Message - From: Burns, John D [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, June 15, 2004 4:16 PM Subject: RE: Select IDs that only have 1 entry I have thought about the same thing.I've also wondered about a NOT DISTINCT command to find records that do have duplicates.Anyone with any bright ideas? John [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
