Re: How to write this MySQL query...
Doesn't the count have to be count(*) but otherwise it looks fine to me. On Sat, Aug 13, 2011 at 3:44 AM, Rick Faircloth r...@whitestonemedia.comwrote: Hi, guys... I've been writing, research, and re-writing a query over and over trying to get the correct (ie, trustworthy) results, but can't make it work. How would I write a query that would: - find all propertyID's from a table that had duplicate photo200 field values I've tried every variation of this I can think of: select propertyID, count(photo200) as NumPhoto200 from rentalphotos group by photo200 having NumPhoto200 1 That's not working. Any suggestions, clues, hints?? Thanks! Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346727 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How to write this MySQL query...
I'd think grouping by propertyID as well would be a good startotherwise you're just getting ALL duplicate photo200 values across ALL properties. What is wrong with your results? that may help us help you Rick ;-) A brute force way is to: select propertyID, count(photo200) as NumPhoto200 from rentalphotos group by propertID, photo200 then a QoQ that does SELECT propertyID FROM [firstQueryName] WHERE NumPhoto200 1 A little ugly but it would get ya past this ;-) Cheers On Fri, 2011-08-12 at 13:44 -0400, Rick Faircloth wrote: Hi, guys... I've been writing, research, and re-writing a query over and over trying to get the correct (ie, trustworthy) results, but can't make it work. How would I write a query that would: - find all propertyID's from a table that had duplicate photo200 field values I've tried every variation of this I can think of: select propertyID, count(photo200) as NumPhoto200 from rentalphotos group by photo200 having NumPhoto200 1 That's not working. Any suggestions, clues, hints?? Thanks! Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346728 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How to write this MySQL query...
- find all propertyID's from a table that had duplicate photo200 field values If you mean _different_ propertyID's sharing the same photo200 value, you would need to count by the photo200 column instead. Then match it with the propertyID. One option is using a join. Another would be a subquery using where exists ... select r.propertyID, pc.photo200, pc.TimesUsed from rentalphotos r inner join ( select photo200, count(*) as TimesUsed from rentalphotos group by photo200 having count(*) 1 ) pc on pc.photo200 = r.photo200 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346729 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: How to write this MySQL query...
Thanks, guys... I decided to try Leigh's suggestion first, and it worked. Thanks for the suggestions, everyone! Rick :o) -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: Friday, August 12, 2011 2:02 PM To: cf-talk Subject: Re: How to write this MySQL query... - find all propertyID's from a table that had duplicate photo200 field values If you mean _different_ propertyID's sharing the same photo200 value, you would need to count by the photo200 column instead. Then match it with the propertyID. One option is using a join. Another would be a subquery using where exists ... select r.propertyID, pc.photo200, pc.TimesUsed from rentalphotos r inner join ( select photo200, count(*) as TimesUsed from rentalphotos group by photo200 having count(*) 1 ) pc on pc.photo200 = r.photo200 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346730 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: How to write this MySQL query...
Rick - If you do not need the TimesUsed count, you might also try the WHERE EXISTS approach to compare performance. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346731 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: How to write this MySQL query...
Thanks, Leigh... I just needed to run a query to see if a client was putting photos with identical names in their database. There were... I need to add a date-time stamp or append a UUID to their filenames, or something to make sure they are unique. The query was just to generate a report and show them why photos were appearing with the wrong properties. Thanks for your help! Rick -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: Friday, August 12, 2011 4:05 PM To: cf-talk Subject: RE: How to write this MySQL query... Rick - If you do not need the TimesUsed count, you might also try the WHERE EXISTS approach to compare performance. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346732 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: How to write this MySQL query...
Ah. Ad-hoc. Gotcha. (Can you tell I have been tuning monster sql queries all day? Here is to a sql free weekend! ;-) -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346733 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
