CODEWITHSUNDEEP
mysqlsql
Khairul
Khairul

Reputation: 425

Left Join base on CASE

I want to make a Left Join in MySQL. The join is base on a condition. For example, if the value is 328, use Left Join <table A> ELSE use Left Join <table B>

Below are the queries:

SELECT * FROM applications app
CASE 
WHEN 
    app.application_type = 328 
THEN
    LEFT JOIN application_individu ai 
    ON app.id = ai.application_id 
ELSE
    LEFT JOIN application_org ao 
    ON app.id = ao.application_id 
END
WHERE app.id=11

I received an error msg on phpmyadmin

#1064 - You have an error in your SQL syntax; 
check the manual that   corresponds to your MariaDB server version for the right syntax to use near 
'(CASE WHEN app.application_type = 328 THEN
 LEFT JOIN application_individu a' at line 5

1. Can we use CASE for join?

2. If possible, how to do it the correct way?

Update Data

Below are the sample data.

applications

id  application_type  app_reference  applicant_type  status created_date
1   330               ZAK00001       328             1      2019-01-16 16:45:14
2   330               ZAK00002       328             1      2019-01-17 12:31:14
3   330               ZAK00003       329             2      2019-01-17 14:31:40

application_individu

id   application_id  name  age  gender  contactNo  ic
1    1               Ali   30   Male    1234       12345678
2    2               Chan  45   Male    1234       54321

application_org

id   application_id  companyName  companyRegNo  picName  picContactNo  companyEmail
1    3               My StartUp   x-123Y        Raja     01035562      [email protected]

parameters

id  parameterValue  detail
1   328             Personal              
2   329             Company
3   1               Complete
4   2               Pending

This is my updated query without the use of CASE.

SELECT * FROM applications app
LEFT JOIN application_individu ai ON app.id=ai.application_id
LEFT JOIN application_org ao ON app.id=ao.application_id
LEFT JOIN parameters param ON app.applicant_type=param.main_id
LEFT JOIN parameters param2 ON app.status=param2.main_id
LEFT JOIN parameters param3 ON app.applicant_type=param3.main_id
LEFT JOIN parameters param4 ON app.status=param4.main_id

Is there way to check if the application is Personal or Company so that I will not repeat the parameters on the join.

Upvotes: 0

Views: 111

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

To join tables conditionally, simply means to outer join them in SQL. So you have either one information or the other in your result row. You'll end up with a query like the one you've added to your request.

A combined parameter table doesn't make sense in my opinion. Have a status table and an applicant_type table instead, so you can apply proper foreign keys and know which is which.

The query would look something like this:

SELECT
  app.app_reference, created_date, s.status_text, apt.type_text,
  ai.name, ai.age,
  ao.companyName, ao.companyRegNo
FROM applications app
INNER JOIN status s ON s.id = app.status_id
INNER JOIN applicant_type apt on apt.id = app.applicant_type_id
LEFT JOIN application_individu ai ON app.id = ai.application_id
LEFT JOIN application_org ao ON app.id = ao.application_id;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You should put the logic in the ON clause. And remember to use COALESCE() in the SELECT:

SELECT a.*,
       COALESCE(ai.name, ao.name) as name
FROM applications a LEFT JOIN
     application_individu ai
     ON a.id = ai.application_id AND
        a.application_type = 328 LEFT JOIN     
    application_org ao 
    ON a.id = ao.application_id  AND
       a.id = 11;

Do not use SELECT * when you are referencing multiple tables. At the very least, you will have naming conflicts when columns have the same name in different tables -- which is very likely to occur in this case.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37483

You can try below - using left join with applying condition in ON cluase

    SELECT * FROM applications app
    left join application_individu ai ON app.id = ai.application_id
    and app.application_type = 328 
    LEFT JOIN application_org ao ON app.id = ao.application_id 
    WHERE app.id=11

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

you could use union

   SELECT app.* FROM applications app    
        LEFT JOIN application_individu ai 
        ON app.id = ai.application_id
    where app.application_type = 328 
union 
SELECT app.* FROM applications app    
       LEFT JOIN application_org ao 
       ON app.id = ao.application_id 
    where app.application_type != 328

Upvotes: 2

Related Questions