how to write this typical mysql query( ho to use subquery column into main query)
- by I Like PHP
I HAVE TWO TABLES shown below
table_joining
id join_id(PK) transfer_id(FK) unit_id transfer_date joining_date
1 j_1 t_1 u_1 2010-06-05 2010-03-05
2 j_2 t_2 u_3 2010-05-10 2010-03-10
3 j_3 t_3 u_6 2010-04-10 2010-01-01
4 j_5 NULL u_3 NULL 2010-06-05
5 j_6 NULL u_4 NULL 2010-05-05
table_transfer
id transfer_id(PK) pastUnitId futureUnitId effective_transfer_date
1 t_1 u_3 u_1 2010-06-05
2 t_2 u_6 u_1 2010-05-10
3 t_3 u_5 u_3 2010-04-10
now i want to know total employee detalis( using join_id) which are currently working on unit u_3 .
means i want only
join_id
j_1 (has transfered but effective_transfer_date is future date, right now in u_3)
j_2 ( tansfered and right now in `u_3` bcoz effective_transfer_date has been passed)
j_6 ( right now in `u_3` and never transfered)
what i need to take care of below steps( as far as i know )
<1> first need to check from table_joining whether transfer_id is NULL or not
<2> if transfer_id= is NULL then see unit_id=u_3 where joining_date <=CURDATE() ( means that person already joined u_3)
<3> if transfer_id is NOT NULL then go to table_transfer using transfer_id (foreign key reference)
<4> now see the effective_transfer_date regrading that transfer_id whether effective_transfer_date<=CURDATE()
<5> if transfer date has been passed(means transfer has been done) then return futureUnitID otherwise return pastUnitID
i used two separate query but don't know how to join those query??
for step <1 ans <2
SELECT unit_id FROM table_joining WHERE joining_date<=CURDATE() AND transfer_id IS NULL AND unit_id='u_3'
for step<5
SELECT IF(effective_transfer_date <= CURDATE(),futureUnitId,pastUnitId) AS currentUnitID FROM table_transfer
// here how do we select only those rows which have currentUnitID='u_3' ??
please guide me the process?? i m just confused with JOINS. i think using LEFT JOIN can return the data i need, but i m not getting how to implement ...please help me.
Thanks for helping me alwayz
