sql error in query

Question

When executing this I get an error:

SELECT  A.company_id,
        B.property_code,
        ISNULL(C.value, B.default_value) as [value]
FROM T_COMPANY A,
    T_PROPERTY_DEFINITION B
    LEFT JOIN [T_PROPERTY_VALUE] C
        ON B.property_id=C.property_id AND A.company_id=C.company_id

Msg 4104, Level 16, State 1, Line 7 The multi-part identifier "A.company_id" could not be bound.

Why is that?

Does the table T_COMPANY contain a column named company_id? — shahkalpesh
– shahkalpesh, Commented Feb 15, 2011 at 9:33
Can I suggest you use more descriptive table name aliases; a random single letter alias does nothing for readability. — Tony
– Tony, Commented Feb 15, 2011 at 9:44

Adrian Pronk · Accepted Answer · 2011-02-15 09:42:53Z

1

Your join clause:

T_PROPERTY_DEFINITION B
LEFT JOIN [T_PROPERTY_VALUE] C
    ON B.property_id=C.property_id AND A.company_id=C.company_id

doesn't contain a table A so you can't refer to it in the 'ON' condition. A is in a separate syntactic block.

answered Feb 15, 2011 at 9:42

Adrian Pronk

14k7 gold badges39 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sachin Shanbhag · Accepted Answer · 2011-02-15 09:36:12Z

0

You are missing a join for table T_Company to T_Property_definition.

You are probably expecting an INNER JOIN between these two which you need to explicitly mention.

answered Feb 15, 2011 at 9:36

Sachin Shanbhag

55.7k11 gold badges92 silver badges103 bronze badges

Comments

marc_s · Accepted Answer · 2011-02-15 09:37:03Z

I would recommend not mixing the old-style (table1, table2, table3) JOIN syntax, and the newer ANSI JOIN syntax (INNER JOIN, LEFT JOIN). I would go with the NEW, standardized ANSI JOIN all the time (much clearer, more intuitive, less risk of an accidental cartesian product).

So use this:

FROM 
   dbo.T_COMPANY A
INNER JOIN
   dbo.T_PROPERTY_DEFINITION B ON A.company_id = B.company_id -- or whatever

Also, I believe you cannot join a single table C to two separate other tables (A and B) in a single JOIN..... so this is most likely invalid:

LEFT JOIN [T_PROPERTY_VALUE] C
    ON B.property_id = C.property_id AND A.company_id = C.company_id

You need to find another way to join those three tables so that the JOINs work - since you're giving us all the info we need, I can only guess - this would be one possible JOIN (join table A to C, and then in a second step C to B) :

SELECT  
   A.company_id,
   B.property_code,
   ISNULL(C.value, B.default_value) as [value]
FROM 
   dbo.T_COMPANY A
LEFT JOIN 
   dbo.[T_PROPERTY_VALUE] C ON A.company_id = C.company_id        
LEFT JOIN
   dbo.T_PROPERTY_DEFINITION B ON B.property_id = C.property_id

Depending on your real needs, you might be able to replace one of the LEFT JOIN by an INNER JOIN

shahkalpesh · Accepted Answer · 2011-02-15 09:40:21Z

0

The SQL doesn't seem correct to me

SELECT  A.company_id,
        B.property_code,
        ISNULL(C.value, B.default_value) as [value]
FROM T_PROPERTY_DEFINITION B
    LEFT JOIN [T_PROPERTY_VALUE] C
        ON B.property_id=C.property_id 
    INNER JOIN T_COMPANY A
        ON A.company_id=C.company_id

Note: I haven't tried this piece of SQL for syntax. This is an example that doesn't mix the JOINing syntax, which is the case in your code.

answered Feb 15, 2011 at 9:40

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Collectives™ on Stack Overflow

sql error in query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related