how to write this typical mysql query( ho to use subquery column into main query)

Posted by I Like PHP on Stack Overflow See other posts from Stack Overflow or by I Like PHP
Published on 2010-05-28T14:26:03Z Indexed on 2010/05/29 5:12 UTC
Read the original article Hit count: 404

Filed under:

mysql-query

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

Developer IT