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
you could use union
You can try below – using left join with applying condition in ON cluase
You should put the logic in the
ON
clause. And remember to useCOALESCE()
in theSELECT
: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.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: