Sample MySQL query for ideal job.

job-query.sql

SELECT
  # Company details
  co.name                   AS company,

  # Job details
  job.overview              AS overview,
  job.title                 AS title,
  job.duration              AS duration,
  job.salary                AS salary,
  job.annual_pto_days       AS pto,
  job.telecommute           AS telecommute,

  # Distance from my ideal geolocation in miles
  # 3959: distance from center to surface of earth
  # 33.70, -117.88: my preferred geolocation (latitude, longitude)
  # Source: http://stackoverflow.com/a/8994850/1093087
  (3959 *
	ACOS(
	  COS(radians(33.70)) * COS(radians(job.latitude)) *
	  COS(radians(job.longitude)-radians(-117.88)) + SIN(radians(33.70)) *
	  SIN(radians(job.latitude))
	)
  )                         AS distance

FROM companies AS co
JOIN jobs AS job ON job.company_id = co.id

WHERE
  job.salary >= 160000 AND
  job.annual_pto_days >= 20 AND
  job.duration = 'permanent'

HAVING
  (distance < 10 OR telecommute = 1)

ORDER BY
  distance ASC,
  salary DESC;
  

sample-data.sql

DROP TABLE IF EXISTS `companies`;
CREATE TABLE IF NOT EXISTS `companies` (
    id int(11) NOT NULL auto_increment,
    name varchar(255) default NULL,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY  (`id`),
    KEY `k_name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `companies` (`name`) VALUES
('Panda Hugs, LLC'),
('Nice Corp');


DROP TABLE IF EXISTS `jobs`;
CREATE TABLE IF NOT EXISTS `jobs` (
    id int(11) NOT NULL auto_increment,
    company_id int(11) default NULL,
    overview varchar(255) default NULL,
    title varchar(255) default NULL,
    duration ENUM('contract', 'permanent') default NULL,
    salary int(11) default NULL,
    annual_pto_days int(11) default NULL,
    telecommute tinyint(1) default NULL,
    longitude decimal(10,2) default NULL,
    latitude decimal(10,2) default NULL,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY  (`id`),
    KEY `k_company_id` (`company_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `jobs` (`company_id`, `overview`, `title`, `duration`, `salary`,
    `annual_pto_days`, `telecommute`, `longitude`, `latitude`) VALUES
((SELECT id FROM companies WHERE name ='Panda Hugs, LLC'), 'Ideal job for you.',
 'Senior Developer', 'permanent', 200000, 25, 0, -117.90, 33.71),
((SELECT id FROM companies WHERE name ='Nice Corp'), 'Good job for you.',
 'Senior Developer', 'permanent', 175000, 20, 0, -117.73, 33.67),
((SELECT id FROM companies WHERE name ='Nice Corp'), 'Long commute.',
 'Senior Developer', 'permanent', 175000, 20, 0, -117.62, 33.43);