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
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License