MySql Join tables based on field value

Question

Is it possible to do something like this:

SELECT 
  p.*, u.*
FROM
  posts AS p 
  IF(p.status = 1) 
  LEFT JOIN users AS u 
    ON u.id = p.user_id 
    ELSE 
  LEFT JOIN pusers AS u 
    ON u.id = p.user_id 
WHERE p.id = 10 ;

Based on post status being true/false join users/pusers table

Gordon Linoff · Accepted Answer · 2015-08-06 18:58:35Z

3

No, but you can do this:

SELECT p.*,
       (case when p.status = 1 then u.col1 else pu.col1 end) as col1
FROM posts p LEFT JOIN
     users u 
     ON u.id = p.user_id and p.status = 1 LEFT JOIN
     pusers AS pu 
     ON pu.id = p.user_id and p.status <> 1
WHERE p.id = 10 ;

In other words, you can join both tables and use the values from the table, based on the condition.

answered Aug 6, 2015 at 18:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

user3176519 Over a year ago

Thanks for your answer, but I'm getting "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'u.col1' in 'field list' [code: 42S22]"

user3176519 Over a year ago

Sorry, my bad. I just needed a valid column name. It works perfect . Thanks.

Collectives™ on Stack Overflow

MySql Join tables based on field value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related