Post reply

Multi-part identifier could not be bound

  • March 8, 2006 at 6:04 pm

    #168615

    I am trying to query a database with 3 tables:

    Person(PersonID, Birthday, Gender)

    Procedure(PatID, Date, CPTCODE, DIAGID1, DIAGID2, DIAGID3, DIAGID4)

    DIAGS(DIAGID, PatID, Date, ICDCODE)

    I am looking to query for PatID, Date, CPTCODE, DIAG1-4, but replace DIAG1, DIAG2... with the corresponiding ICDCODE.

    If I run something like ...select ICDCODE FROM DIAGS WHERE Procedure.DIAGID1 = DIAGS.DIAGID, I get the error message ..."the multi-part identifier Procedure.DIAG1 could not be bound." I'm sure that I'm not providing the arguements to define a single row but I'm brand new to SQL and I'm not sure how to fix it. Any help greatly appreciated.

    Steve

  • March 8, 2006 at 9:21 pm

    #625585

    i'm guessing table joining and reserved word issues here?

    does this look a little better?

    SELECT ICDCODE FROM DIAGS

    INNER JOIN [PROCEDURE] ON  DIAGS.DIAGID = Procedure.DIAGID

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 9, 2006 at 9:31 pm

    #625848

    Avoid using reserved word as your table name or column name.

  • August 4, 2006 at 7:48 am

    #653322

    One possible reason for the error msg "The multi-part identifier could not be bound" could be that you're referencing a table or a view alias BEFORE joining the concerned table/view

    In your case it seems you're calling Procedure.DIAGID1 while there is no table or view by the name or alias of PROCEDURE in the rest of the query.

    And oh yeah, PROCEDURE is a reserved keyword!

  • January 4, 2008 at 2:54 am

    #764733

    Hi all

    I have the same problem, i referencing a table before the Join, how do I solve it?

  • January 4, 2008 at 3:03 am

    #764736

    Could you post the query that you'e having problems with please?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2008 at 3:37 am

    #764746

    ALTER PROCEDURE [Eft].[SelectEditState]

    (

    @TranID varchar(max)

    )

    CREATE TABLE #TranID (id varchar(max) )

    IF @TranID is not null

    BEGIN

    INSERT INTO #TranID select item FROM dbo.CommaSplit(@TranID)

    END

    SELECT

    dbo.State.StateId, t.[dateTime] as [dateTime], p.partner as partner, dbo.State.State as [State] , t.tenderAmt / 100 as tenderAmt

    FROM #TranID tId Join eft.[Tran] t on tId.id = eft.[Tran].TranID, dbo.State

    JOIN Partner p ON t.partnerID = p.partnerID

  • January 4, 2008 at 3:58 am

    #764749

    dedezana (1/4/2008)

    FROM #TranID tId Join eft.[Tran] t on tId.id = eft.[Tran].TranID, dbo.State

    JOIN Partner p ON t.partnerID = p.partnerID

    Is the cross join to the State table intentional? If not, you're missing a join on State.

    Maybe this is what you need? (additions and changes in red)

    FROM #TranID tId Join eft.[Tran] t on tId.id = t.TranID

    INNER JOIN dbo.State ON dbo.State.SomeColumn = ....

    JOIN Partner p ON t.partnerID = p.partnerID

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2008 at 4:14 am

    #764752

    hi

    Give me 1/2 hour to fix thing from asp.net and I'll get back to you.....

  • January 4, 2008 at 4:18 am

    #764753

    I use the dbo.State table for the another fields why do you need to inner join it.....i'm new at database more of web developer

  • January 4, 2008 at 4:36 am

    #764760

    If you include it in the from and don't do a join, you get what's called a cross join. Each row of the one table is matched to each row of the second. Say we have 2 tables as follows

    DECLARE @tbl1 TABLE (ID1 Char(1))

    DECLARE @tbl2 TABLE (ID2 smallint)

    INSERT INTO @tbl1 VALUES ('A')

    INSERT INTO @tbl1 VALUES ('B')

    INSERT INTO @tbl1 VALUES ('C')

    INSERT INTO @tbl1 VALUES ('D')

    INSERT INTO @tbl2 VALUES (4)

    INSERT INTO @tbl2 VALUES (3)

    INSERT INTO @tbl2 VALUES (2)

    INSERT INTO @tbl2 VALUES (1)

    SELECT * FROM @tbl1, @tbl2 -- no join.

    The select returns 16 rows. Every possible combination of the rows of the two tables

    ID1ID2

    A4

    B4

    C4

    D4

    A3

    B3

    C3

    D3

    A2

    B2

    C2

    D2

    A1

    B1

    C1

    D1

    If that's what you want, great (though watch for the row counts, as cross join quicly leads to massive number of rows)

    If not, then you need to find out how the state table is related to the others in the query and join it in as appropriate

    How many rows are in State?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2008 at 5:13 am

    #764772

    FROM #TranID tId Join eft.[Tran] t on tId.id = t.TranID

    INNER JOIN dbo.State ON dbo.State.StateId = t.StateId

    JOIN Partner p ON t.partnerID = p.partnerID

    But the result are wrong, I want the TranId to join the State, but what is happening is the is the State that is selected is the one from INNER JOIN dbo.State ON dbo.State.StateId = t.StateId

  • January 4, 2008 at 5:21 am

    #764773

    I'm sorry, I don't understand what you want.

    Please read through this and give me some info so that I can help you.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 13 posts - 1 through 12 (of 12 total)

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