Three Table Query Including One Left Join

  • SparTodd

    SSChasing Mays

    Points: 600

    Hi,

    Even with my limited experience, I should be able to figure this out, but I'm stumped.

    In SQL Server 2005 I have a DB2 Database on a linked server. There are three tables that I'm trying to select from in a particular query. The tables are CLMMSTP, PHMSTP and RPMSTP.

    The tables CLMMSTP and PHMSTP have a 1:1 relationship on the columns CLIENT and DOCTOR.

    The tables CLMMSTP and RPMSTP have a Left Join relationship on the columns CLIENT and REFDOC. The Left Join is required because not all records in CLMMSTP have a REFDOC.

    So here is the SQL query that I wrote:

    SELECT CLMMSTP.CLIENT, CLMMSTP.CLMNUM, CLMMSTP.MACTNM, CLMMSTP.DOCTOR,

    CLMMSTP.DOS, PHMSTP.PHNAME,

    RPMSTP.REFDOC, RPMSTP.RNAME

    FROM TXBTESTDTA.CLMMSTP, TXBTESTDTA.PHMSTP

    LEFT JOIN TXBTESTDTA.RPMSTP

    ON CLMMSTP.CLIENT = RPMSTP.CLIENT AND CLMMSTP.REFDOC = RPMSTP.REFDOC

    WHERE (CLMMSTP.CLIENT = PHMSTP.CLIENT)

    AND (CLMMSTP.DOCTOR = PHMSTP.DOCTOR) AND (CLMMSTP.CLIENT = N'ACA')

    AND (CLMMSTP.MACTNM = '985') AND (CLMMSTP.CLMNUM = '8268060')

    Yet, this is the result I'm getting:

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "CLMMSTP.CLIENT" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "CLMMSTP.REFDOC" could not be bound.

    Any help on this would be appreciated.

  • J-F Bergeron

    SSCrazy Eights

    Points: 8593

    SparTodd (8/27/2009)


    Hi,

    Even with my limited experience, I should be able to figure this out, but I'm stumped.

    In SQL Server 2005 I have a DB2 Database on a linked server. There are three tables that I'm trying to select from in a particular query. The tables are CLMMSTP, PHMSTP and RPMSTP.

    The tables CLMMSTP and PHMSTP have a 1:1 relationship on the columns CLIENT and DOCTOR.

    The tables CLMMSTP and RPMSTP have a Left Join relationship on the columns CLIENT and REFDOC. The Left Join is required because not all records in CLMMSTP have a REFDOC.

    So here is the SQL query that I wrote:

    SELECT CLMMSTP.CLIENT, CLMMSTP.CLMNUM, CLMMSTP.MACTNM, CLMMSTP.DOCTOR,

    CLMMSTP.DOS, PHMSTP.PHNAME,

    RPMSTP.REFDOC, RPMSTP.RNAME

    FROM TXBTESTDTA.CLMMSTP, TXBTESTDTA.PHMSTP

    LEFT JOIN TXBTESTDTA.RPMSTP

    ON CLMMSTP.CLIENT = RPMSTP.CLIENT AND CLMMSTP.REFDOC = RPMSTP.REFDOC

    WHERE (CLMMSTP.CLIENT = PHMSTP.CLIENT)

    AND (CLMMSTP.DOCTOR = PHMSTP.DOCTOR) AND (CLMMSTP.CLIENT = N'ACA')

    AND (CLMMSTP.MACTNM = '985') AND (CLMMSTP.CLMNUM = '8268060')

    Yet, this is the result I'm getting:

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "CLMMSTP.CLIENT" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "CLMMSTP.REFDOC" could not be bound.

    Any help on this would be appreciated.

    Does this query work for you?

    SELECT CLMMSTP.CLIENT,

    CLMMSTP.CLMNUM,

    CLMMSTP.MACTNM,

    CLMMSTP.DOCTOR,

    CLMMSTP.DOS,

    PHMSTP.PHNAME,

    RPMSTP.REFDOC,

    RPMSTP.RNAME

    FROM TXBTESTDTA.CLMMSTP

    INNER JOIN TXBTESTDTA.PHMSTP

    ON CLMMSTP.CLIENT = PHMSTP.CLIENT

    AND CLMMSTP.DOCTOR = PHMSTP.DOCTOR

    LEFT JOIN TXBTESTDTA.RPMSTP

    ON CLMMSTP.CLIENT = RPMSTP.CLIENT

    AND CLMMSTP.REFDOC = RPMSTP.REFDOC

    WHERE (CLMMSTP.CLIENT = N'ACA')

    AND (CLMMSTP.MACTNM = '985')

    AND (CLMMSTP.CLMNUM = '8268060')

    /*(CLMMSTP.CLIENT = PHMSTP.CLIENT)

    AND (CLMMSTP.DOCTOR = PHMSTP.DOCTOR)

    AND */

    Cheers,

    J-F

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    The error:

    You have to use aliases for your tables or use a complete qualified column name like "SELECT TXBTESTDTA.CLMMSTP.CIENT".

    BTW:

    Try to get rid of the old JOIN syntax as combination of FROM and WHERE. Use the ANSI JOIN:

    SELECT

    cl.CLIENT

    ,cl.CLMNUM

    ,cl.MACTNM

    ,cl.DOCTOR

    ,cl.DOS

    ,pc.PHNAME

    ,rp.REFDOC

    ,rp.RNAME

    FROM TXBTESTDTA.CLMMSTP cl

    JOIN TXBTESTDTA.PHMSTP pc ON cl.CLIENT = pc.CLIENT AND cl.DOCTOR = pc.DOCTOR

    LEFT JOIN TXBTESTDTA.RPMSTP rp ON cl.CLIENT = rp.CLIENT AND cl.REFDOC = rp.REFDOC

    WHERE (cl.MACTNM = '985')

    AND (cl.CLMNUM = '8268060')

    Greets

    Flo

  • SparTodd

    SSChasing Mays

    Points: 600

    J-F Bergeron (8/27/2009)


    SparTodd (8/27/2009)


    Hi,

    Even with my limited experience, I should be able to figure this out, but I'm stumped.

    In SQL Server 2005 I have a DB2 Database on a linked server. There are three tables that I'm trying to select from in a particular query. The tables are CLMMSTP, PHMSTP and RPMSTP.

    The tables CLMMSTP and PHMSTP have a 1:1 relationship on the columns CLIENT and DOCTOR.

    The tables CLMMSTP and RPMSTP have a Left Join relationship on the columns CLIENT and REFDOC. The Left Join is required because not all records in CLMMSTP have a REFDOC.

    So here is the SQL query that I wrote:

    SELECT CLMMSTP.CLIENT, CLMMSTP.CLMNUM, CLMMSTP.MACTNM, CLMMSTP.DOCTOR,

    CLMMSTP.DOS, PHMSTP.PHNAME,

    RPMSTP.REFDOC, RPMSTP.RNAME

    FROM TXBTESTDTA.CLMMSTP, TXBTESTDTA.PHMSTP

    LEFT JOIN TXBTESTDTA.RPMSTP

    ON CLMMSTP.CLIENT = RPMSTP.CLIENT AND CLMMSTP.REFDOC = RPMSTP.REFDOC

    WHERE (CLMMSTP.CLIENT = PHMSTP.CLIENT)

    AND (CLMMSTP.DOCTOR = PHMSTP.DOCTOR) AND (CLMMSTP.CLIENT = N'ACA')

    AND (CLMMSTP.MACTNM = '985') AND (CLMMSTP.CLMNUM = '8268060')

    Yet, this is the result I'm getting:

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "CLMMSTP.CLIENT" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "CLMMSTP.REFDOC" could not be bound.

    Any help on this would be appreciated.

    Does this query work for you?

    SELECT CLMMSTP.CLIENT,

    CLMMSTP.CLMNUM,

    CLMMSTP.MACTNM,

    CLMMSTP.DOCTOR,

    CLMMSTP.DOS,

    PHMSTP.PHNAME,

    RPMSTP.REFDOC,

    RPMSTP.RNAME

    FROM TXBTESTDTA.CLMMSTP

    INNER JOIN TXBTESTDTA.PHMSTP

    ON CLMMSTP.CLIENT = PHMSTP.CLIENT

    AND CLMMSTP.DOCTOR = PHMSTP.DOCTOR

    LEFT JOIN TXBTESTDTA.RPMSTP

    ON CLMMSTP.CLIENT = RPMSTP.CLIENT

    AND CLMMSTP.REFDOC = RPMSTP.REFDOC

    WHERE (CLMMSTP.CLIENT = N'ACA')

    AND (CLMMSTP.MACTNM = '985')

    AND (CLMMSTP.CLMNUM = '8268060')

    /*(CLMMSTP.CLIENT = PHMSTP.CLIENT)

    AND (CLMMSTP.DOCTOR = PHMSTP.DOCTOR)

    AND */

    Thank you very much, J-F! That works and most importantly, I get it.

    EDIT: Do we mark these threads as resolved and attribute a particular response to the resolution on here? Sorry, I'm a newbie here but I'm not seeing that option.

  • J-F Bergeron

    SSCrazy Eights

    Points: 8593

    You can't mark it as the answer here, but just saying it is enough.

    Thanks for the feedback, and as Flo noted, I also propose you use table aliases, they are a lot easier to work with, and shorter to write.

    Cheers,

    J-F

  • SparTodd

    SSChasing Mays

    Points: 600

    Thank for your insight, too, Flo.

Viewing 6 posts - 1 through 6 (of 6 total)

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