How to put where clauses in an inner join clause?

craftdeer 0
Viewed 30

I am joining a few tables and in each join, I have to specify a specific condition.

SELECT * FROM
table1
INNER JOIN table2 
ON table1.id = table2.id WHERE table2.column1 = 'horse'
INNER JOIN table3
ON table2.id = table3.id WHERE table3.column1 = 'cow';

I get a sql syntax error. When I remove the where statements, it starts working again. How am I able to solve this?

h2 db2 mysql sql
2 Answers

Change your wheres on the joins to ands:

SELECT * FROM
table1
INNER JOIN table2 
ON table1.id = table2.id AND table2.column1 = 'horse'
INNER JOIN table3
ON table2.id = table3.id AND table3.column1 = 'cow';
Sadly Full Stack0

There's just one WHERE clause for the entire query, they're not associated with joins. Use AND or OR to combine them.

SELECT * 
FROM table1
INNER JOIN table2 ON table1.id = table2.id 
INNER JOIN table3 ON table2.id = table3.id 
WHERE table3.column1 = 'cow' AND table2.column1 = 'horse'
Barmar0
Related