Is this join not possible?

Hi
"cmay" wrote:

> Ok maybe I am having a senior moment here or something, I thought this
> was something you could do, but maybe I am just remembering wrong.
With SQL 2005 this can be done with a function and APPLY
http://msdn2.microsoft.com/en-us/library/ms175156.aspx
> I am trying to join Customers and Addresses, but I only 1 a maximum of
> 1 address joined. So in other words, if I did a join and there was a
> customer w/ 5 addresses, that would result in 5 records being
> returned, and I just want it to give me 1 record with the first
> address joined.
> I am using this Select statement:
> SELECT C.*, A.*
> FROM Customers C
> LEFT OUTER JOIN
> (SELECT TOP 1 *
> FROM CustomerAddresses
> WHERE (CustomerAddresses.CustomerId = C.CustomerId)) AS A
> ON C.CustomerId = A.CustomerId
> It is saying that "C.CustomerId" is not valid in my nested select
> statement.
If you are using SQL 2000 then assumming you have an AddressId primary on
your address table, you could try
SELECT C.*, A.*
FROM Customers C
LEFT JOIN (
(SELECT CustomerId, MAX(AddressId) AS AddressId
FROM CustomerAddresses
GROUP BY CustomerId ) B
JOIN CustomerAddresses A ON B.AddressId = A.AddressId AND B.CustomerId
= A.CustomerId ) ON C.CustomerId = A.CustomerId AND C.CustomerId =
B.CustomerId

> I can probably do this with a join to a user defined function
> returning a table, but I could have sworn that what I was trying to do
> was do-able...
> Can anyone help?
>
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0E784E06-484B-44B2-803F-2EF3E53FAE1E@.microsoft.com...[vbcol=seagreen]
> Hi
> "cmay" wrote:
> With SQL 2005 this can be done with a function and APPLY
> http://msdn2.microsoft.com/en-us/library/ms175156.aspx
> If you are using SQL 2000 then assumming you have an AddressId primary on
> your address table, you could try
> SELECT C.*, A.*
> FROM Customers C
> LEFT JOIN (
> (SELECT CustomerId, MAX(AddressId) AS AddressId
> FROM CustomerAddresses
> GROUP BY CustomerId ) B
> JOIN CustomerAddresses A ON B.AddressId = A.AddressId AND
> B.CustomerId
> = A.CustomerId ) ON C.CustomerId = A.CustomerId AND C.CustomerId =
> B.CustomerId
Or you can use OUTER APPLY or ROwNumber.
These are from AdventureWorks
SELECT C.ContactID, A.AddressID
FROM HumanResources.Employee C
outer apply
(
SELECT TOP 1 *
FROM HumanResources.EmployeeAddress EA
WHERE (EA.EmployeeID = c.EmployeeID)
) AS A
order by contactID
with ca(ContactID,AddressID,RowNum)
as(
SELECT
C.ContactID,
ea.AddressID,
ROW_NUMBER() over (partition by c.ContactID order by ea.AddressID) rownum
FROM HumanResources.Employee C
left join HumanResources.EmployeeAddress EA
on EA.EmployeeID = c.EmployeeID
)
select ContactID,AddressID
from ca
order by ContactID
David