skip to Main Content

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.

4

Answers


  1. 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 
    
    Login or Signup to reply.
  2. 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
    
    Login or Signup to reply.
  3. 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.

    Login or Signup to reply.
  4. 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;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search