#   qrySampleRailroad.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 tblSampleRailroad;


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

  FROM tblGeoStateCA

  WHERE (frequency_assigned >= '00000160.21500000' AND
         frequency_assigned <= '00000161.56500000')

    AND NOT
         (entity_name LIKE '%BURLINGTON NORTHERN%'
           OR
          entity_name LIKE '%CSX%'
           OR
          entity_name LIKE '%BNSF%'
           OR
          entity_name LIKE '%BN&SF%'
           OR
          entity_name LIKE '%FILLMORE%'
           OR
          entity_name LIKE '%ALAMEDA COORIDOR%'
           OR
          entity_name LIKE '%RAILROAD%'
           OR
          entity_name LIKE '%RAIL%'
           OR
          entity_name LIKE '%BAY AREA%'
           OR
          entity_name LIKE '%VERMONT%'
           OR
          entity_name LIKE '%TRANSIT%'
           OR
          entity_name LIKE '%LOCOMOTIVE%'
           OR
          entity_name LIKE '%AUTHORITY%'
           OR
          entity_name LIKE '%CORRIDOR%'
           OR
          entity_name LIKE '%TROLLEY%'
           OR
          entity_name LIKE '%NORFOLK%'
           OR
          entity_name LIKE '%SOUTHERN CALIFORNIA REGIONAL RAIL AUTHORITY%'
           OR
          entity_name LIKE '%AAR%'
           OR
          entity_name LIKE '%SPCRR%'
           OR
          entity_name LIKE '%B&R%'
           OR
          entity_name LIKE '%PACIFIC HARBOR%'
           OR
          entity_name LIKE '%SOUTHERN PACIFIC%'
           OR
          entity_name LIKE '%UNION PACIFIC%')

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