#   qryOrgStateCA.sql
#   SQL
#   n6lhv@arrl.net (Wayne Smith)


#   Display the MySQL version
SELECT version();


#  Switch to the correct database
USE uls


#  delete the table if it exists (it probably does)
DROP TABLE IF EXISTS tblOrgStateCA;


#  Join the relevant data into a "flat" table
CREATE TABLE tblOrgStateCA
#EXPLAIN
  SELECT DISTINCT *

  FROM tblGeoStateCA

#  WHERE entity_name = 'CALIFORNIA, STATE OF'
  WHERE (entity_name LIKE '%CAL%' AND
         entity_name LIKE '%STATE%' AND
         entity_name LIKE '%OF%')
         AND NOT
        (entity_name LIKE '%BANK%' OR
         entity_name LIKE '%BAR%' OR
         entity_name LIKE '%FAIR%' OR
         entity_name LIKE '%KNIGHTS OF COLUMBUS%' OR
         entity_name LIKE '%MASONS%' OR
         entity_name LIKE '%PARADISE VALLEY%' OR
         entity_name LIKE '%UNIVERSITY%' OR
         entity_name LIKE '%VALLEY HEALTH%')

  ORDER BY frequency_assigned,
      call_sign,
      radio_service_code,
      class_station_code,
      location_state,
      location_county,
      location_city,
      location_address
  ;