CODEWITHSUNDEEP
phpmysqljoin
Vahn
Vahn

Reputation: 542

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 }
else
{ INNER JOIN TaskID_2 ON assignment.TaskID = TaskID_2.TaskID }
WHERE assignment.ID = 'some_variable'

nb:

Upvotes: 3

Views: 65

Answers (1)

Shadow
Shadow

Reputation: 34284

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

Upvotes: 2

Related Questions