#   qryGeoStateCA.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 tblGeoStateCA;


#  Join the relevant data into a "flat" table
CREATE TABLE tblGeoStateCA
#EXPLAIN
  SELECT DISTINCT
      PUBACC_FR.frequency_assigned            ,
      PUBACC_FR.call_sign                     ,

      PUBACC_HD.license_status                ,
      PUBACC_HD.radio_service_code            ,

      PUBACC_FR.class_station_code            ,

      PUBACC_LO.location_state                ,
      PUBACC_LO.location_county               ,
      PUBACC_LO.location_city                 ,
      PUBACC_LO.location_address              ,
      PUBACC_LO.location_name                 ,

      tblGeoStateCA1.contact_licensee         ,

#      PUBACC_EN.entity_type                   ,
      PUBACC_EN.entity_name                   ,
      PUBACC_EN.state                         ,
      PUBACC_EN.city                          ,
      PUBACC_EN.street_address                ,
#      PUBACC_EN.attention_line                ,
#      PUBACC_EN.first_name                    ,
#      PUBACC_EN.mi                            ,
#      PUBACC_EN.last_name                     ,
#      PUBACC_EN.suffix                        ,
#      PUBACC_EN.phone                         ,
#      PUBACC_EN.fax                           ,
#      PUBACC_EN.email                         ,
#      PUBACC_EN.zip_code                      ,
#      PUBACC_EN.po_box                        ,
#      PUBACC_EN.sgin                          ,
#      PUBACC_EN.frn                           ,
#      PUBACC_EN.licensee_id                   ,

      PUBACC_CP.state_code                    ,
      PUBACC_CP.control_county                ,
      PUBACC_CP.control_city                  ,
      PUBACC_CP.control_address               ,
      PUBACC_CP.control_phone                 ,

#      PUBACC_LO.units_hand_held               ,
#      PUBACC_LO.units_temp_fixed              ,
#      PUBACC_LO.units_mobile                  ,
#      PUBACC_LO.units_aircraft                ,
#      PUBACC_LO.units_itinerant

#      PUBACC_EM.emission_code                 ,
#      PUBACC_EM.digital_mod_rate              ,
#      PUBACC_EM.digital_mod_type              ,

      PUBACC_FR.cnt_mobile_units              ,
#      PUBACC_FR.cnt_mob_pagers                ,

      PUBACC_HD.grant_date                    ,
      PUBACC_HD.expired_date                  ,
      PUBACC_HD.cancellation_date             ,
      PUBACC_HD.effective_date                ,
      PUBACC_HD.last_action_date              ,
      PUBACC_FR.unique_system_identifier      ,
      PUBACC_FR.ULS_File_Number               ,

      PUBACC_LO.ground_elevation              ,
      PUBACC_LO.lat_degrees                   ,
      PUBACC_LO.lat_minutes                   ,
      PUBACC_LO.lat_seconds                   ,
      PUBACC_LO.lat_direction                 ,
      PUBACC_LO.long_degrees                  ,
      PUBACC_LO.long_minutes                  ,
      PUBACC_LO.long_seconds                  ,
      PUBACC_LO.long_direction                ,

      PUBACC_FR.frequency_upper_band          ,
      PUBACC_FR.frequency_carrier             ,

      PUBACC_FR.db_id

#  FROM PUBACC_CP, PUBACC_EM, PUBACC_EN, PUBACC_FR, PUBACC_HD, PUBACC_LO
  FROM PUBACC_LO

        LEFT JOIN PUBACC_CP ON PUBACC_LO.call_sign=PUBACC_CP.call_sign
        INNER JOIN PUBACC_EN ON PUBACC_LO.call_sign=PUBACC_EN.call_sign
        INNER JOIN PUBACC_HD ON PUBACC_LO.call_sign=PUBACC_HD.call_sign
        INNER JOIN PUBACC_FR ON PUBACC_LO.call_sign=PUBACC_FR.call_sign
        LEFT JOIN tblGeoStateCA1 ON PUBACC_LO.call_sign=tblGeoStateCA1.call_sign
# don't use...the query doesn't finish (why? oh why?)
#        INNER JOIN PUBACC_EM ON PUBACC_LO.call_sign=PUBACC_EM.call_sign

## testing only
#  FROM PUBACC_LO
#
#        LEFT JOIN PUBACC_CP ON PUBACC_LO.call_sign=PUBACC_CP.call_sign
#        LEFT JOIN PUBACC_EN ON PUBACC_LO.call_sign=PUBACC_EN.call_sign
#        LEFT JOIN PUBACC_HD ON PUBACC_LO.call_sign=PUBACC_HD.call_sign
#        LEFT JOIN PUBACC_FR ON PUBACC_LO.call_sign=PUBACC_FR.call_sign
#        LEFT JOIN tblGeoStateCA1 ON PUBACC_LO.call_sign=tblGeoStateCA1.call_sign
##        INNER JOIN PUBACC_EM ON PUBACC_LO.call_sign=PUBACC_EM.call_sign

# If I am going to delete the NonCA's earlier in the process,
# then this clause is redundant...ws
#        AND (PUBACC_LO.location_state='CA' OR PUBACC_LO.location_state='')

#  WHERE convert( PUBACC_FR.frequency_assigned, DECIMAL ) > 2036

  WHERE (PUBACC_EN.entity_type = 'L') AND
        (location_state='CA' OR state='CA' OR state_code='CA')
#        (PUBACC_FR.call_sign='KES328')

#    OR
#    ((PUBACC_LO.lat_direction = 'N' AND PUBACC_LO.lat_degrees < 42) OR
#    (PUBACC_LO.long_direction = 'W' AND PUBACC_LO.long_degrees > 114))

#  HAVING (location_state='CA' OR state='CA' OR state_code='CA')
#  HAVING (location_state='CA' OR state='CA' OR state_code='CA') OR
#    (PUBACC_LO.lat_direction = 'N' AND PUBACC_LO.lat_degrees < 35) OR
#    (PUBACC_LO.long_direction = 'W' AND PUBACC_LO.long_degrees > 114)

  ORDER BY PUBACC_FR.frequency_assigned,
      PUBACC_FR.call_sign,
      PUBACC_HD.license_status,
      PUBACC_HD.radio_service_code,
      PUBACC_FR.class_station_code,
      PUBACC_LO.location_state,
      PUBACC_LO.location_county,
      PUBACC_LO.location_city,
      PUBACC_LO.location_address
  ;