#   qrySampleLASDSimplex.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 tblSampleLASDSimplex;


#  Join the relevant data into a "flat" table
#  (Note: "subselects" in MySQL require version MySQL 4.1 or higher)
CREATE TEMPORARY TABLE tblSampleLASDSimplexTemp
#EXPLAIN
  SELECT DISTINCT Replace(frequency_assigned, '473', '470')

  FROM tblOrgCountyLosAngeles

  WHERE (frequency_assigned >= '00000473.00000000' AND
         frequency_assigned <= '00000476.00000000')
  ;

CREATE TABLE tblSampleLASDSimplex
#EXPLAIN
  SELECT DISTINCT *

  FROM tblOrgCountyLosAngeles

  WHERE (frequency_assigned >= '00000470.00000000' AND
         frequency_assigned <= '00000473.00000000')

#   this hangs the computer...but I don't know why just yet...ws
#  AND frequency_assigned NOT IN
#         (SELECT Replace(frequency_assigned, '473', '470')
#          FROM tblOrgCountyLosAngeles
#          WHERE (frequency_assigned >= '00000473.00000000' AND
#                 frequency_assigned <= '00000476.00000000'))

  AND frequency_assigned NOT IN
         (SELECT * FROM tblSampleLASDSimplexTemp)

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


#  Join the relevant data into a "flat" table
#  (Note: "subselects" in MySQL require version MySQL 4.1 or higher)
#CREATE TEMPORARY TABLE tblSampleLASD-simplex1
#EXPLAIN
#  SELECT DISTINCT *, Format(Round(Convert(frequency_assigned AS signed), 4) + 3, 'xxxxxxxx.xxxxxxxx') AS frequency_assigned-InputPair
#
#  FROM tblOrgCountyLosAngeles
#
#  WHERE (frequency_assigned >= '00000470.00000000' AND
#               frequency_assigned <= '00000473.00000000')
#
#  ORDER BY frequency_assigned,
#      call_sign,
#      radio_service_code,
#      class_station_code,
#      location_state,
#      location_county,
#      location_city,
#      location_address
#  ;