Vahn Vahn - 1 year ago 55
MySQL Question

How to add different join in a query

I have a assignment table:

ID Name TaskID
A1 John A
A2 Roy B
A3 Clair A1

This is TaskID_1 table (all column should be selected):

TaskID Deadline supervisor division letterID organizer
A 10
B 20

This is TaskID_2 table (all column should be selected) :

TaskID Deadline place_of_arrival staffID day_of_shipment
A1 100
A2 200

So, I'd like to join in different table.

If the query is (
SELECT * FROM assignment where id ='A3'
) then the Task ID will be A1, so I'd like to join with Task ID2 table.

But if the query is (
SELECT * FROM assignment where id ='A2'
) then the Task ID will be B, so I'd like to join with Task ID1 table.

Is it possible ?
It's something like this (not a valid SQL query) :

SELECT * FROM assignment
IF(assignment.TaskID IN (SELECT TaskID FROM TaskID_1))
{ INNER JOIN TaskID_1 ON assignment.TaskID = TaskID_1.TaskID }
{ INNER JOIN TaskID_2 ON assignment.TaskID = TaskID_2.TaskID }
WHERE assignment.ID = 'some_variable'


  • I am using Code Igniter so the script is in active record

  • I don't have the privilege to change the table structure.

Answer Source

Conditional joins do not exists in sql in general. Either you join a table or you do not, but you have to make up your mind.

What you can do to achive the expected out come is 2 outer joins to the assignment table, or a union of 2 queries that inner join the assignment table to one of the 2 taskid tables. I'll show you the outer join version. In the select list either use coalesce() to return the first non-null value, or list fields from both tables and make the decision in the calling application as to which field's data to use.

select a.*, coalesce(t1.deadline, t2.deadline) as deadline from assignment a
left join taskid_1 t1 on a.taskid=t1.taskid
left join taskid_2 t2 on a.taskid=t2.taskid
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download