sql error in query

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?

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.

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.

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

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.