CODEWITHSUNDEEP
mysqljoinif-statementconditional-statements
plugowski
plugowski

Reputation: 323

MySQL JOIN with IF conditions

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?

Upvotes: 26

Views: 98895

Answers (4)

Ludo - Off the record
Ludo - Off the record

Reputation: 5533

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)

Upvotes: 31

Saurabh Mishra
Saurabh Mishra

Reputation: 127

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.

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,

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.

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

Upvotes: 0

Tomalak
Tomalak

Reputation: 338128

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

Upvotes: 9

cEz
cEz

Reputation: 5062

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.

Upvotes: 28

Related Questions