#   qryOrgAnchor.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 tblOrgAnchor;


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

  FROM tblGeoStateCA

  WHERE (entity_name LIKE '%NORDS%' AND NOT
        (entity_name LIKE '%RACK%' OR entity_name LIKE '%DISTRIBUTION%'))
     OR
        (entity_name LIKE '%SEARS%' AND NOT
        (entity_name LIKE '%LOGISTICS%' OR entity_name LIKE '%RACEWAY%'))
     OR
        ((entity_name LIKE '%ROBINSON%' AND entity_name LIKE '%MAY%') AND NOT
        (entity_name LIKE '%DISMANTLING%'))
     OR
        ((entity_name LIKE '%MACY%' OR entity_name LIKE '%MAYC%') AND NOT
        (entity_name LIKE '%PHARMACY%' OR entity_name LIKE '%FLYING%' OR entity_name LIKE '%VICTOR%'))
     OR
        ((entity_name LIKE '%PENNEY%' OR entity_name LIKE '%JC%') AND NOT
        (entity_name LIKE '%KJC%' OR entity_name LIKE '%SJC%' OR
         entity_name LIKE '%RJC%' OR entity_name LIKE '%JCW%'))

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