带有 IF 条件的 MySQL JOIN

2022-01-20 00:00:00 join if-statement conditional-statements mysql

我想通过查询获得一些结果，类似于:

I want to get some results via query simillar to:

SELECT 
    * FROM
    users LEFT JOIN
    IF (users.type = '1', 'private','company') AS details ON
    users.id = details.user_id WHERE
    users.id = 1

有什么想法吗?

推荐答案

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

在这个例子中，private 有 col1、col2 和 col3 列，而 company 有 col1 和 col4，但你想要它们全部.

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

相关文章