Mobile Dev Queries
- Query to find the bike trains with more than a single occurrence
SELECT /* dg.description, */ dg.displayName, bt.routeName, count(1) rec_count FROM `DisplayGroup` dg join MAP_ELEMENT_GROUP map on map.group_id = dg.group_id join DisplayElement de on de.element_id = map.element_id join BikeTrain bt on bt.id = de.sourceid where de.className = 'BikeTrain' group by dg.displayName, bt.routeName having count(1) > 1 order by count(1) desc
- List of the details needed to remove those records
select map.group_id, dg.displayName, de.* from DisplayElement de left outer join BikeTrain bt on bt.id = de.sourceId join MAP_ELEMENT_GROUP map on map.element_id = de.element_id join DisplayGroup dg on dg.group_id = map.group_id where bt.routeName is null
- List users who are in the database, but aren't associated with a Bike Train:
select u.firstName, u.lastName, utMap.trainId, bt.routeName from User u left outer join UserTrainMap utMap on u.id = utMap.userId left outer join BikeTrain bt on utMap.trainId = bt.id where routeName is null
- Removing unused bike train records
delete bt from BikeTrain bt left outer join DisplayElement de on de.sourceId = bt.id where de.element_id is null
- Duplicated Bike Trains
SELECT `routeName`,`Description`, count(1) FROM `BikeTrainGroup` group by routeName, Description having count(1) > 1
page revision: 6, last edited: 09 Aug 2014 21:57