CODEWITHSUNDEEP
mysqlsqldatabaseinner-join
Magearlik
Magearlik

Reputation: 523

Why doesn't this SQL statement work

I have two tables. Job and Location. I then want to query the database to get jobs that are in a certain location. Here are my database tables.

CREATE TABLE `job` (
  `id` int(11) NOT NULL,
  `title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `company_id` int(11) NOT NULL,
  `contact_email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `contact_telephone` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `is_active` tinyint(4) NOT NULL,
  `salary_id` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `tenure_id` int(11) NOT NULL,
  `work_type_id` int(11) NOT NULL,
  `occupation_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `views` int(11) NOT NULL,
  `img_url` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

AND

CREATE TABLE `location` (
  `id` int(11) NOT NULL,
  `region` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `state` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Why doesn't this query create an inner join between job and location?

SELECT * FROM job, location 
WHERE location.region = 'Central Coast' 
OR location.region = 'Hunter Valley' 
OR location.region = 'Illawarra' 
AND job.location_id = location.id

Upvotes: 1

Views: 59

Answers (2)

jarlh
jarlh

Reputation: 44766

Switch to modern, explicit JOIN syntax, as it is easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.

And use IN:

SELECT *
FROM job
JOIN location ON job.location_id = location.id
WHERE location.region IN ('Central Coast', 'Hunter Valley', 'llawarra')

Upvotes: 3

Andomar
Andomar

Reputation: 238076

Because and has a higher precedence than or.

This means your where clause is executed as:

WHERE  location.region = 'Central Coast' 
       OR location.region = 'Hunter Valley' 
       OR (location.region = 'Illawarra' AND job.location_id = location.id)

Upvotes: 0

Related Questions