Alan.B (4/16/2015)
Looks like you have duplicate column names in both tables but you are not using a table aliasYou query should look something like this:
SELECT a.col1, a.col2, b.col1FROM a
Join
(
<Subquery>
) AS b
ON a.col1 = b.col2
Can you post the DDL for both tables?
Further on Alan's request, how about pitching in some usable sample data in the form of an insert statement?
😎
BTW, this is my guess of how the query should look like, mind you that once an alias is defined for an object/table, it goes by that alias, no schema qualification is applicable after that point (just like marriage:-P)
SELECT
ORD.CustomerID
,MAX(ORD.OrderDate) AS LastOrder
,MIN(ORD.OrderDate) AS FirstOrder
,COUNT(ORD.OrderID) AS [TotalOrders]
FROM dbo.Orders ORD
INNER JOIN
(
SELECT
Top 5 CustomerID As TopCustomer
FROM dbo.customers
group by customerid
order by TopCustomer desc
) As TopCustomer
ON ORD.CustomerID = TopCustomer.TopCustomer;