Skip to content

Instantly share code, notes, and snippets.

@tatwell tatwell/job-query.sql
Last active Jul 23, 2019

Embed
What would you like to do?
Sample MySQL query for ideal job.
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;
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.