Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Post Unanswered: join on key SOMETIMES.....?

    I need to join a table on a field with conditions.
    --------------------------------------------------

    ------current join
    LEFT OUTER JOIN GL on
    (
    (LINKS.accthigh >= GL.acctnu)
    and
    (LINKS.acctLow <= GL.acctnu)
    )
    and LINKS.dept = GL.dept and
    and LINKS.fund = GL.fund


    ------needed something like this...but I am sure not correct syntax
    LEFT OUTER JOIN GL on
    (
    (LINKS.accthigh >= GL.acct)
    and
    (LINKS.acctLow <= GL.acct)
    )
    if links.deptnu <> -1
    LINKS.dept = GL.deptnu and
    if links.fundnu <> -1
    LINKS.fund = GL.fundNu

    --------------------------------------------------
    The field LINKS.fund or LINKS.dept can have a value of -1 which means to join beyond the dept or fund boundaries (ie any fund or any dept).

    Is this possible or do I need to looking for a new approach?

    Thanks for your help!
    -R

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this:
    Code:
    LEFT OUTER
      JOIN GL 
        on LINKS.accthigh >= GL.acct
       and LINKS.acctLow <= GL.acct
       and LINKS.dept =
           ( case when links.deptnu <> -1 
                  then GL.deptnu 
                  else LINKS.dept end )
       and LINKS.fund =
           ( case when links.fundnu <> -1 
                  then GL.fundNu 
                  else LINKS.fund end )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Thats it!

    Thats exactly what I was looking for!

    Thanks again for the help!

    GBY,
    -R

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •