CODEWITHSUNDEEP
mysqlsql
cksrc
cksrc

Reputation: 2347

inner join with condition on joined table

I'm trying to collect data using the following query:

SELECT * 
FROM `table1` AS `t1` 
INNER JOIN `table2` AS `t2` 
  ON `t1`.`id`=`t2`.`id` 
WHERE  `t2`.`name`='myname'

It looks like the WHERE condition is ignored. Is it possible to apply the WHERE condition on the joined table?

Upvotes: 1

Views: 1178

Answers (2)

maximpa
maximpa

Reputation: 1988

It's always a good idea to use LIKE keyword with '%' sign before and after the search string for troubleshooting the problem:

-- This would match the following:
-- ' myname'
-- 'myname '
-- Wow! Who put that space in there?.. :)
WHERE t2.name LIKE '%myname%'

Also, there might be a case sensitive collation set on the table. To make the query case insensitive you can use Collate keyword:

-- Case doesn't matter now
WHERE t2.name COLLATE Latin1_General_CI_AS = 'mYnAmE'

About the combining the conditions...

MSDN: Using Inner Joins

In the ISO standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that ISO supports in the WHERE clause. Inner joins specified in the WHERE clause are known as old-style inner joins.

So, the combined conditions would look like:

SELECT *
FROM table1 AS t1
INNER JOIN table2 AS t2
  ON t1.id = t2.id AND t2.name = 'myname'
  -- No 'where' clause required

Here is a similar post on stackoverflow: SQL join: where clause vs. on clause.

Upvotes: 1

Vivek
Vivek

Reputation: 1680

The 'Where' clause should not be ignored in this condition as well.

If you want to exclude the where condition, you can do it by writing the condition as part of the ON Clause.

SELECT * 
FROM `table1` AS `t1` 
INNER JOIN `table2` AS `t2` 
  ON `t1`.`id`=`t2`.`id`  
    and `t2`.`name`='myname'

Upvotes: 5

Related Questions