Douglas Fentiman wrote: > Appreciate some suggestions on creating a query. > > -Retreiving records with a four digit id number. > -Parent records end in '00' (i.e. 3100), child records end with '01' to '99' > (i.e. 3102). > -Need to exclude parent record if child record(s) exist. > -return parent records if no child record(s). > > In other words: > > If record id's are: > > 3100 > 3101 > 3102 > 3200 > 3300 > > Records 3101, 3102, 3200, 3300 are returned, but 3100 is not. > > Any ideas? > > Using MySQL 5 >
SELECT * FROM thetable a WHERE ROUND(theid,-2) <> theid OR (ROUND(theid,-2) = theid AND (SELECT count(*) FROM thetable b WHERE b.theid > a.theid AND b.theid <= a.theid + 99) = 0) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2761 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
