outer join as substitute for except

  • I am trying to rewrite an SQL2005 except construction to a outer join for the benefit of SQL2000 instances, but joining 2 tempsets is giving me a hard time.

    I got the following:

    SELECT LR.* FROM

    (

    (SELECT DISTINCT P.PROID,

    0 AS PUBID1,

    0 AS PUBID2,

    TEMP.PUBID AS PUBTYPEID1,

    TEMP2.PUBID AS PUBTYPEID2,

    '-' AS MON_SO_PLUS,

    '-' AS MON_SV_PLUS,

    '-' AS MON_SB_PLUS,

    '-' AS MON_EB_PLUS,

    '-' AS MON_EP_PLUS

    FROM TABLE1 P

    CROSS JOIN

    (SELECT PT.PUBID FROM TABLE2 PT WHERE PT.PUBID > -1999 ) TEMP

    CROSS JOIN

    (SELECT PT.PUBID FROM TABLE2 PT WHERE PT.PUBID > -1999 ) TEMP2) L

    LEFT OUTER JOIN

    (Select

    P1.PROID AS PROID,

    P1.PUBTYPEID AS PUBTYPEID1,

    P2.PUBTYPEID AS PUBTYPEID2

    from TABLE1 P1

    INNER JOIN TABLE1 P2

    ON P1.PROID = P2.PROID) R

    ON

    L.PROID = R.PROID AND L.PUBTYPEID1 = R.PUBTYPEID1 AND L.PUBTYPEID2 = R.PUBTYPEID2

    WHERE R.PROID IS NULL

    ) LR

    Can't get it to work. Is it something with aliasing the temp sets?

    Appreciate any input

    [the result is unioned with a third temp set as a final statement]

  • Never mind.. Forgot the explicit outer select :w00t:

  • I'd suggest to use WHERE NOT EXISTS.

    OUTER JOIN sucks in some situations.

    _____________
    Code for TallyGenerator

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

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