#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    2
    Rep Power
    0

    MS SQL Left Join multiple tables


    hi,

    I want to left join table2 and table3 to table1;
    - with date filtering from table1 column

    error message;

    Msg 209, Level 16, State 1, Line 27
    Ambiguous column name 'Posting Date'.
    Msg 209, Level 16, State 1, Line 28
    Ambiguous column name 'Posting Date'.

    thank you.

    -------------------------------------

    USE [database]

    SELECT
    [SSI$Purch_ Inv_ Line].[Line No_]
    ,[SSI$Purch_ Inv_ Line].[Buy-from Vendor No_]
    ,[SSI$Purch_ Inv_ Line].[Document No_]
    ,[SSI$Purch_ Inv_ Line].Description
    ,[SSI$Purch_ Inv_ Line].[VAT Prod_ Posting Group]
    ,[SSI$Purch_ Inv_ Line].[Posting Date]
    ,[SSI$Purch_ Inv_ Line].[VAT Base Amount]
    ,[SSI$Purch_ Inv_ Line].[Amount Including VAT]
    ,[SSI$Purch_ Inv_ Line].Amount
    ,SSI$Vendor.No_
    ,SSI$Vendor.Name
    ,SSI$Vendor.[VAT Registration No_]
    ,[SSI$VAT Entry].[Entry No_]
    ,[SSI$VAT Entry].[Bill-to_Pay-to No_]
    FROM [SSI$Purch_ Inv_ Line]

    LEFT JOIN SSI$Vendor
    ON [SSI$Purch_ Inv_ Line].[Buy-from Vendor No_]=SSI$Vendor.No_

    LEFT JOIN [SSI$VAT Entry]
    ON [SSI$Purch_ Inv_ Line].[Buy-from Vendor No_]=[SSI$VAT Entry].[Bill-to_Pay-to No_]

    WHERE
    [Posting Date] >= '10/01/2010' AND
    [Posting Date] <= '10/31/2010'

    ORDER BY [Document No_]
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Originally Posted by glennyboy
    Msg 209, Level 16, State 1, Line 27
    Ambiguous column name 'Posting Date'.
    Msg 209, Level 16, State 1, Line 28
    Ambiguous column name 'Posting Date'.
    it's prettty obvious what the problem is

    change this --
    Code:
    WHERE
    	[Posting Date] >= '10/01/2010' AND
    	[Posting Date] <= '10/31/2010'
    to this --
    Code:
    WHERE
    	[SSI$Purch_ Inv_ Line].[Posting Date] >= '10/01/2010' AND
    	[SSI$Purch_ Inv_ Line].[Posting Date] <= '10/31/2010'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    2
    Rep Power
    0
    thank you for this. highly appreciated

    Originally Posted by r937
    it's prettty obvious what the problem is

    change this --
    Code:
    WHERE
    	[Posting Date] >= '10/01/2010' AND
    	[Posting Date] <= '10/31/2010'
    to this --
    Code:
    WHERE
    	[SSI$Purch_ Inv_ Line].[Posting Date] >= '10/01/2010' AND
    	[SSI$Purch_ Inv_ Line].[Posting Date] <= '10/31/2010'

IMN logo majestic logo threadwatch logo seochat tools logo