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


#   Display the MySQL version
SELECT version();


#  Switch to the correct database
USE uls


#  generate the query
#SELECT DISTINCT a.call_sign, a.frequency_assigned, b.frequency_assigned, a.entity_name
#  FROM (
#    SELECT DISTINCT call_sign, frequency_assigned, entity_name FROM tblGeoStateCA
#    WHERE frequency_assigned >= "935" AND frequency_assigned <= "941"
#  ) as a
#  INNER JOIN (
#    SELECT DISTINCT call_sign, frequency_assigned, entity_name FROM tblGeoStateCA
#    WHERE frequency_assigned >= "896" AND frequency_assigned <= "902"
#  ) as b
#    ON a.call_sign = b.call_sign
#  WHERE NOT( convert( a.frequency_assigned, DECIMAL ) - 39 = convert( b.frequency_assigned, DECIMAL ))
#;


#  generate the query
CREATE TEMPORARY TABLE tblAnomaly1
#EXPLAIN
SELECT DISTINCT call_sign, frequency_assignedNumber, entity_name
  FROM tblGeoStateCAFreqNumeric FORCE INDEX (frequency_assignedNumber)
    WHERE frequency_assignedNumber >= 935 AND frequency_assignedNumber <= 941
;

#   Create an index to prepare for a future JOIN
ALTER TABLE tblAnomaly1
      ADD INDEX(call_sign)
;

#  generate the query
CREATE TEMPORARY TABLE tblAnomaly2
#EXPLAIN
SELECT DISTINCT call_sign, frequency_assignedNumber, entity_name
  FROM tblGeoStateCAFreqNumeric FORCE INDEX (frequency_assignedNumber)
    WHERE frequency_assignedNumber >= 896 AND frequency_assignedNumber <= 902
;

#   Create an index to prepare for a future JOIN
ALTER TABLE tblAnomaly2
      ADD INDEX(call_sign)
;

#  generate the query
##EXPLAIN
#SELECT DISTINCT a.call_sign, a.frequency_assignedNumber, a.entity_name, b.call_sign, b.frequency_assignedNumber, b.entity_name
#  FROM tblAnomaly1 as a JOIN tblAnomaly2 as b ON a.call_sign = b.call_sign
#  WHERE a.call_sign = b.call_sign AND
#    NOT( a.frequency_assignedNumber - 39 = b.frequency_assignedNumber )
#;


#  generate the query
#EXPLAIN
#SELECT DISTINCT a.call_sign, a.frequency_assignedNumber, a.entity_name, b.call_sign, b.frequency_assignedNumber, b.entity_name
SELECT DISTINCT a.call_sign
  FROM tblAnomaly1 as a JOIN tblAnomaly2 as b ON a.call_sign = b.call_sign
  WHERE (
    a.call_sign = b.call_sign AND a.entity_name = b.entity_name AND
    a.frequency_assignedNumber - 39 NOT IN ( b.frequency_assignedNumber )
  )
;


#  generate the query
#EXPLAIN
#SELECT DISTINCT a.call_sign, a.frequency_assignedNumber, a.entity_name, b.call_sign, b.frequency_assignedNumber, b.entity_name
SELECT DISTINCT call_sign
  FROM tblAnomaly1
  WHERE (
    frequency_assignedNumber - 39 NOT IN ( SELECT frequency_assignedNumber FROM tblAnomaly2 )
  )
;