About Mysql join case when error

Question

select t.* FROM user_tq t join 
CASE when t.blogid = 0 then user_dp ELSE  user_blog END b
on t.uid = b.uid where ***;

I want to join different table according to blogid, when blogid is 0, join user_dp, else join user_blog.But it returns 1064 error. How to solve this problem?

possible duplicate of [MySQL query where JOIN depends on CASE](http://stackoverflow.com/questions/6994843/mysql-query-where-join-depends-on-case) — artm, Aug 07 '15 at 08:46

score 0 · Accepted Answer · edited May 23 '17 at 12:23

You can do this with left join and filter the right result by COALESCE

select t.* 
FROM user_tq t 
left join user_dp ud on t.blogid = 0 and t.uid = ud.uid
left join user_blog ub on t.blogid != 0 and t.uid = ub.uid
where
COALESCE(ud.uid, ub.uid) IS NOT NULL and
***;

For more info refer : MySQL query where JOIN depends on CASE

If you are trying to inner join to filter data on condition then;

select * from (
    select t.* 
    FROM user_tq t 
    join user_dp ud on t.blogid = 0 and t.uid = ud.uid
    union all
    select t.* 
    FROM user_tq t 
    join user_blog ub on t.blogid != 0 and t.uid = ub.uid
) as x
where ****;

score 0 · Answer 2 · answered Aug 07 '15 at 09:12

USE:

select * FROM users t
left join user_role r ON t.user_id = 0 AND r.user_id = t.user_id
left join product_category c ON t.user_id != 0 AND c.user_id = t.user_id
WHERE COALESCE(r.user_id, c.user_id) IS NOT NULL
WHERE ***;

Replace users, user_role and product_category with your tables.

wally · Answer 3 · 2015-10-08T22:51:10.600

The other answers posted here are the correct solutions. I'll answer the "Why do I get 1064" for you.

The syntax you've written is invalid. You cannot "conditionally" join to a table like this.

What you can do is LEFT JOIN - which as opposed to [INNER] JOIN means "return NULL for all fields on this table if no record matches the join condition".

There's lots on the Internet about LEFT vs INNER joins, and MySQL's website documents the COALESCE function very well.

Have a play and a read, you'll figure it out why the other solutions work.

Upon reflection, I felt this could benefit from some additional explanation.

See this SQLFiddle: http://sqlfiddle.com/#!9/043b7/6

About Mysql join case when error

3 Answers3