MySQL JOIN with IF conditions

SELECT * FROM users 
LEFT JOIN private AS details ON users.id = details.user_id 
WHERE users.id = 1 AND users.type = 1

UNION

SELECT * FROM users 
LEFT JOIN company AS details ON users.id = details.user_id 
WHERE users.id = 1 AND users.type != 1

I think that is what you are trying to do, isn't it?

As you have now said that the number of columns differs, you would need to specify the columns, e.g.

SELECT 'private' AS detailType, users.*, col1, col2, col3, '' FROM users 
LEFT JOIN private AS details ON users.id = details.user_id 
WHERE users.id = 1 AND users.type = 1

UNION

SELECT 'company', users.*, col1, '', '', col4  FROM users 
LEFT JOIN company AS details ON users.id = details.user_id 
WHERE users.id = 1 AND users.type != 1

In this example, private has columns col1, col2 and col3, whilst company has col1 and col4, but you want them all.

I'm sure this is already resolved, but for people with a similar problem.

You can use multiple left joins to get the data from both tables, then use an IF() to determine which of the column(s) you want as your result.

SELECT *, IF (users.type = 1, p.name, c.name) AS name FROM users
LEFT JOIN private AS p ON (users.type = 1 AND users.id = p.user_id) 
LEFT JOIN company AS c ON (users.type != 1 AND users.id = c.user_id)

SELECT
  users.*,
  details.info,
  CASE users.type WHEN '1' THEN 'private' ELSE 'company' END AS user_type
FROM
  users
  INNER JOIN (
    SELECT user_id, info FROM private
    UNION
    SELECT user_id, info FROM company
  ) AS details ON details.user_id = users.id

---

EDIT: Original version of the answer (question misunderstood):

SELECT
  *, 
  CASE type WHEN '1' THEN 'private' ELSE 'company' END AS details
FROM
  users
WHERE
  users.id = 1