带有 IF 条件的 MySQL JOIN

MySQL JOIN with IF conditions

提问by plugowski

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

Any ideas?

有任何想法吗？

回答by Cez

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.

在此示例中，private 具有 col1、col2 和 col3 列，而 company 具有 col1 和 col4，但您需要它们全部。

回答by Ludo - Off the record

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

我相信这已经解决了，但对于有类似问题的人。

You can also try to multiple left joins to get all the data

您也可以尝试多个左连接以获取所有数据

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)

回答by Tomalak

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

回答by Saurabh-Magento2-Developer

Here I am sharing the if else condition in Magento2 SQL query to get the report order details customers log in and not login both, you have just enhanced your query according to the situation.

在这里，我分享了 Magento2 SQL 查询中的 if else 条件来获取报告订单详细信息客户登录而不是同时登录，您只是根据情况增强了您的查询。

SELECT 
    so.increment_id as ID,
    sog.customer_id as Customer_Id,
    sog.customer_name as Customer_Name,
    sog.customer_email as Customer_Email,
    CASE sog.customer_group 
        WHEN '1' THEN 'Custome Login'
        ELSE 'Not Login'
    END as Customer_Group,
    sog.grand_total as Grand_Total,
    sog.subtotal as Subtotal,
    sog.billing_name as Billing_Name,
    sog.billing_address as Billing_Address,
    sog.shipping_address as shipping_address,
    so.shipping_description as Shipping_Information,
    so.status as Status,
    so.cancel_order_username as Canceled_BY,
    so.cancel_order_currenttime as Cancellation_Time,
    so.cancel_order_comment as Cancellation_Reason
FROM sales_order so
LEFT JOIN sales_order_grid as sog ON sog.increment_id=so.increment_id
WHERE so.cancel_order_currenttime >= Date('2018-10-01')
    AND so.cancel_order_currenttime <= Date('2018-12-05')

Here we have created some alias according to the situation: so->sales_order table, sog->sales_order_grid,

这里我们根据情况创建了一些别名： so->sales_order table, sog->sales_order_grid,

and we are using the if/else condition in the customer group because we know that, "0"is used for the Guest user, "1"is used for the login user and both are used for the customer group.

我们在客户组中使用 if/else 条件，因为我们知道，"0"用于来宾用户，"1"用于登录用户，并且都用于客户组。

I hope this suggestion will help you with your confusion, and please let me know if you face any issue in understanding this answer.

我希望这个建议能帮助你解决你的困惑，如果你在理解这个答案时遇到任何问题，请告诉我。