COnditional JOIN query in MySQL

Question

I have two mysql tables

user:

|--------------------------------|
| id | name  | type | ruser_type |
|--------------------------------|
|  1 | Admin | a    |            |
|  2 |       | r    |      c     |
|--------------------------------|

customer

|-------------------------|
|  id  |  name  | user_id | 
|-------------------------|
|  1   |  Sam   |    2    |
|-------------------------|

If user.type is 'a' or 's', then its admin user whose name is in user table.

If user.type is 'r' and ruser_type is 'c', then its regular user which has a relation in customer table where customer.user_id = user.id

I want a query which would run a conditional join. If user.type is 'a' or 's', then name would be fetched from user table.

If user.type is 'r' and and ruser_type is 'c', then name would be fetched from customer table with the JOIN condition customer.user_id = user.id.

For this, I have written a query like this:-

SELECT users.fname as adminFname, customers.fname as customerFname, users.type FROM users
LEFT JOIN customers ON (customers.user_id = users.id AND 
                            ( 
                                (users.type = 'r' AND users.ruser_type = 'c') 
                                    OR users.type = 'a' 
                                    OR users.type = 's'
                            )
                       )
                        WHERE users.id = 1

Is there any possibility to optimize the query more?

Also, how can I write this query using Laravel eloquent?

a lazy way would be to `UNION` the results of 2 queries. one for admins and one for customers. — Tuckbros, Sep 20 '19 at 06:26
another solution can be found there :https://stackoverflow.com/questions/4706100/mysql-equivalent-of-decode-function-in-oracle — Tuckbros, Sep 20 '19 at 06:26
As well as SHOW CREATE TABLE statements for all relevant tables, questions about query optimisation always require the EXPLAIN for the given query. — Strawberry, Sep 20 '19 at 06:28

score 0 · Answer 1 · answered Sep 20 '19 at 06:32

FWIW, I find this marginally easier to read...

SELECT u.fname adminFname
     , c.fname customerFname
     , u.type 
  FROM users u
  LEFT 
  JOIN customers c
    ON c.user_id = u.id 
 WHERE u.id = 1
   AND (
        (u.type = 'r' AND u.ruser_type = 'c')
     OR (u.type IN('a','s'))
       )

score 0 · Answer 2 · answered Sep 20 '19 at 06:36

I have written two sql query hope this will help you

SELECT CASE CU.type WHEN 'a' OR 's' THEN CU.name END AS name,
CASE WHEN CU.type = 'r' AND CU.ruser_type = 'c' THEN CR.name END AS cust_name, CU.type
FROM 
user AS CU
LEFT JOIN customer AS CR ON CR.user_id = CU.id

In this you'll get result like this,

name    cust_name   type
          Sam         r
Admin                 a

and i have wrote another query like this,

SELECT CASE WHEN CU.type  = 'a' OR 's' THEN CU.name ELSE CR.name END AS name, CU.type
FROM 
user AS CU
LEFT JOIN customer AS CR ON CR.user_id = CU.id

In this you'll get result like this

name    type
Sam     r
Admin   a

DB File Link https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=b02d931b68a4f70d8b4a84144c60a572

You can use where condition with id in both query to get id wise result. — Meet Soni, Sep 20 '19 at 06:45

score 0 · Answer 3 · answered Sep 20 '19 at 07:32

This will give you required result for all users:

SELECT u.fname adminFname
     , c.fname customerFname
     , u.type 
  FROM users u
  LEFT JOIN customers c
    ON (u.type = 'r' AND u.ruser_type = 'c' AND c.user_id = u.id)

Add where condition as required.

You can even simplify it further to get common firstName column in output:

SELECT COALESCE(u.fname, c.fname) firstName, u.type 
  FROM users u
  LEFT JOIN customers c
    ON (u.type = 'r' AND u.ruser_type = 'c' AND c.user_id = u.id)

COnditional JOIN query in MySQL

3 Answers3