Post reply

Retrieve record even if the joined table doesn''t contains the record

  • February 23, 2006 at 12:24 pm

    #113412

    Hi all,

    I need to join 3 tables, UPR00100 is master employee table, one employee one record, same as UPR00102. However, UPR30300 is the paycheck table, not every employee get paid in every pay check period.

    I need to get result even if the checkdate is null, how to do it?  Thank you.

    ------------------------------------------------------

    SELECT  u1.EMPLOYID, u1.LASTNAME, u1.FRSTNAME, u2.ADDRESS1, u2.CITY, u2.STATE, u2.ZIPCODE, COALESCE (u3.CHEKDATE,'') as CHEKDATE

    FROM   UPR00100 u1 ,UPR00102 u2 ,UPR30300 u3

    WHERE u3.year1 = 2006 and u1.EMPLOYID='999999999' and u3.chekdate='2/14/2006' and u1.EMPLOYID = u3.EMPLOYID AND u2.EMPLOYID = u3.EMPLOYID AND u1.jobtitle = u3.jobtitle and u1.EMPLOYID = u2.EMPLOYID

  • February 23, 2006 at 12:27 pm

    #623040

    LEFT OUTER JOIN

  • February 23, 2006 at 12:42 pm

    #623045

    SELECT  u1.EMPLOYID, u1.LASTNAME, u1.FRSTNAME, u2.ADDRESS1, u2.CITY, u2.STATE, u2.ZIPCODE, IsNull(u3.CHEKDATE,'') as CHEKDATE

    FROM   UPR00100 u1

    inner join UPR00102 u2

    on u1.EMPLOYID = u2.EMPLOYID

    left outer join UPR30300 u3

    on u1.EMPLOYID = u3.EMPLOYID and u1.jobtitle = u3.jobtitle and u3.year1 = 2006 and u3.chekdate='2/14/2006'

    WHERE  u1.EMPLOYID='999999999' 

    --NOTE: I believe IsNULL is faster then COALESCE and does the same thing if only one value is being checked.  I reserve the use of COALASCE to COALASCE(Col1, Col2, ColN, '')

  • February 23, 2006 at 12:44 pm

    #623048

    Thank you.  I got it.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply