#   qrySampleFederal.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 tblSampleFederal;


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

  FROM tblGeoStateCA

  WHERE ((frequency_assigned >= '00000162.00000000' AND
         frequency_assigned <= '00000174.00000000')
     OR
        (frequency_assigned >= '00000406.00000000' AND
         frequency_assigned <= '00000420.00000000'))

    AND NOT
         (frequency_assigned = '00000163.25000000' OR
          frequency_assigned = '00000166.25000000' OR
          frequency_assigned = '00000169.25000000' OR
          frequency_assigned = '00000170.15000000' OR
          frequency_assigned = '00000170.24500000' OR
          frequency_assigned = '00000170.25000000' OR
          frequency_assigned = '00000170.27500000' OR
          frequency_assigned = '00000170.30000000' OR
          frequency_assigned = '00000170.30500000' OR
          frequency_assigned = '00000170.32500000' OR
          frequency_assigned = '00000171.02500000' OR
          frequency_assigned = '00000171.04500000' OR
          frequency_assigned = '00000171.05000000' OR
          frequency_assigned = '00000173.22500000' OR
          frequency_assigned = '00000173.27500000' OR
          frequency_assigned = '00000173.32500000' OR
          frequency_assigned = '00000173.35000000' OR
          frequency_assigned = '00000173.37500000' OR
          frequency_assigned = '00000173.39000000' OR
          frequency_assigned = '00000174.00000000')

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