Inner join syntax error in mysql select statement

Question

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?

What are the seemingly redundant conditions like AND last name = last name supposed to do? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Sep 2, 2013 at 23:51
That was from my code, where I generate a sql statement from a GUI interface. The where clauses are not really relavent here. I was just testing my GUI.... — omega
– omega, Commented Sep 2, 2013 at 23:52

Matthew · Accepted Answer · 2013-09-03 02:47:14Z

1

Few things:

Your Where clause comes after the join - which is likely your problem
Hard to understand what is going on with some of the field names you are using
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?

edited Sep 3, 2013 at 2:47

answered Sep 2, 2013 at 23:46

Matthew

9,9584 gold badges50 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

omega Over a year ago

I edited my above code. I saw an example of intersection, and I am trying to make my code look like it.

Matthew Over a year ago

@omega Updated with a suggestion to fix, and a suggestion for how to begin testing if that does not work.

Collectives™ on Stack Overflow

Inner join syntax error in mysql select statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related