CODEWITHSUNDEEP
mysqlsql
omega
omega

Reputation: 43833

Inner join syntax error in mysql select statement

This is my MySQL select statement, where I am trying to do an intersection:

SELECT id 
FROM
(SELECT id 
 FROM members WHERE id!=15 AND `last name` = `last name` AND (`first name` = "James") AND `email address` = `email address` AND `mobile number` = `mobile number` AND type_id = type_id AND active = active
) AS A

INNER JOIN

SELECT id 
FROM
(SELECT DISTINCT m.members_id as id 
 FROM map m 
 WHERE m.members_id!=15 AND (((SELECT count(*) FROM tasks) = 0) OR (((SELECT count(*) FROM checklist WHERE map_id=m.id) / (SELECT count(*) FROM tasks)) * 100 >= 0 AND ((SELECT count(*) FROM checklist WHERE map_id=m.id) / (SELECT count(*) FROM tasks)) * 100 <= 100)) AND m.`topic` = m.`topic` AND m.`location` = m.`location` AND m.`country` = m.`country` AND m.`city` = m.`city` AND m.`organization` = m.`organization`
) AS B

USING (id)

This is the example intersect code I saw from

SELECT DISTINCT value FROM table_a
INNER JOIN table_b
USING (value);

From: Alternative to Intersect in MySQL

However I am getting a syntax error near "inner join". Does anyone know whats wrong here?

Upvotes: 0

Views: 1367

Answers (1)

Matthew
Matthew

Reputation: 9949

Few things:

  1. Your Where clause comes after the join - which is likely your problem
  2. Hard to understand what is going on with some of the field names you are using
  3. Going to try and fix your formatting a bit and provide other advice, but that is hard to read.

Just looking at making your query look like the one you are using as a template I would expect the result to be something like:

SELECT id 
FROM
(SELECT id 
 FROM members WHERE id!=15 AND `last name` = `last name` AND (`first name` = "James") AND `email address` = `email address` AND `mobile number` = `mobile number` AND type_id = type_id AND active = active
) AS A

INNER JOIN

(SELECT DISTINCT m.members_id as id 
 FROM map m 
 WHERE m.members_id!=15 AND (((SELECT count(*) FROM tasks) = 0) OR (((SELECT count(*) FROM checklist WHERE map_id=m.id) / (SELECT count(*) FROM tasks)) * 100 >= 0 AND ((SELECT count(*) FROM checklist WHERE map_id=m.id) / (SELECT count(*) FROM tasks)) * 100 <= 100)) AND m.`topic` = m.`topic` AND m.`location` = m.`location` AND m.`country` = m.`country` AND m.`city` = m.`city` AND m.`organization` = m.`organization`
) AS B

USING (id)

Removing that select id from after the inner join. That said can you simplify your query and see if you can get that working first - you have a lot of conditional logic in your sub-selects which makes it hard to test in a fiddle, etc. Maybe post a fiddle for an example of your data and query?

Upvotes: 1

Related Questions