SQL Syntax Error...

[fletchsod]

I've been working on this for 2 hours and still dont get it on why I'm having syntax error... I'm using Common Table Expression for SQL 2008.

SELECT tmpTable3.CompanyName3, tmpTable3.AccountID3, tmpTable1.TotalPurchaseCount1, tmpTable2.TotalSoldCount2

FROM

(

      SELECT AccountID AS AccountID1, COUNT(RawID) AS TotalPurchaseCount1 FROM tblPurchaseRaw

      GROUP BY tblPurchaseRaw.AccountID

)

AS tmpTable1

LEFT OUTER JOIN

(

      SELECT AccountID AS AccountID2, COUNT(RawID) AS TotalSoldCount2 FROM tblSoldRaw

      GROUP BY tblSoldRaw.AccountID

)

AS tmpTable2

LEFT OUTER JOIN

(

      SELECT AccountID AS AccountID3, CompanyName AS CompanyName3 FROM tblaAccounts

)

AS tmpTable3

ON tmpTable3.AccountID3 = tmpTable1.AccountID1

AND tmpTable3.AccountID3 = tmpTable2.AccountID2

The error message is "The multi-part identifier “tmpTable1.AccountID1” could not be bound"...

Thanks...

 

[markros]

I don't see an error, but I'd re-write it using CTE as

;with cte1 as (SELECT AccountID AS AccountID1, COUNT(RawID) AS TotalPurchaseCount1 FROM tblPurchaseRaw

      GROUP BY tblPurchaseRaw.AccountID
),

cte2 as (second select),

cte3 as (third select)

select cte1.*, cte2.*, cte3.* from cte1 
LEFT JOIN cte2 on cte1.AccountID1 = cte2.AccountID2
LEFT JOIN cte3 on cte1.AccountID1 = cte3.AccountID3

PluralSight Learning Library

 

[fletchsod]

Thanks...

 

[SQLBill]

What happens if you change the ON to this:

ON tmpTable3.AccountID = tmpTable1.AccountID

AND tmpTable3.AccountID = tmpTable2.AccountID

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

I see the problem.

The original query is not using CTE's, but is using derived tables. In fact, there are 3 derived tables. To illustrate the problem, let's remove the queries for the derived tables and look at the query.

SELECT tmpTable3.CompanyName3, tmpTable3.AccountID3, tmpTable1.TotalPurchaseCount1, tmpTable2.TotalSoldCount2

FROM

(Query 1)

AS tmpTable1

LEFT OUTER JOIN

(Query 2)

AS tmpTable2

LEFT OUTER JOIN

(Query 3)

AS tmpTable3

ON tmpTable3.AccountID3 = tmpTable1.AccountID1

AND tmpTable3.AccountID3 = tmpTable2.AccountID2

Now let's reformat (without changing any code).

SELECT tmpTable3.CompanyName3, tmpTable3.AccountID3, tmpTable1.TotalPurchaseCount1, tmpTable2.TotalSoldCount2
FROM   (Query 1) AS tmpTable1
       LEFT OUTER JOIN (Query 2) AS tmpTable2
       LEFT OUTER JOIN (Query 3) AS tmpTable3
         ON  tmpTable3.AccountID3 = tmpTable1.AccountID1
         AND tmpTable3.AccountID3 = tmpTable2.AccountID2

Notice that we have 3 "tables" but only one ON clause. This is your problem. Change the AND to ON, like this:

SELECT tmpTable3.CompanyName3, tmpTable3.AccountID3, tmpTable1.TotalPurchaseCount1, tmpTable2.TotalSoldCount2

FROM

(

      SELECT AccountID AS AccountID1, COUNT(RawID) AS TotalPurchaseCount1 FROM tblPurchaseRaw

      GROUP BY tblPurchaseRaw.AccountID

)

AS tmpTable1

LEFT OUTER JOIN

(

      SELECT AccountID AS AccountID2, COUNT(RawID) AS TotalSoldCount2 FROM tblSoldRaw

      GROUP BY tblSoldRaw.AccountID

)

AS tmpTable2

LEFT OUTER JOIN

(

      SELECT AccountID AS AccountID3, CompanyName AS CompanyName3 FROM tblaAccounts

)

AS tmpTable3

ON tmpTable3.AccountID3 = tmpTable1.AccountID1

[!]ON[/!] tmpTable3.AccountID3 = tmpTable2.AccountID2

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, the JOIN clause didn't look correct to me as well. Besides, even if it's permissible to write JOIN conditions at the end, writing JOIN conditions right after the JOIN seems much more intuitive and explanatory to me.

PluralSight Learning Library

 

[gmmastros]

It is permissible to write all your ON clauses at the end, but I don't like it, and I won't endorse it either.

What is not permissible is to have 3 tables with only one ON clause.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom