Is this join not possible?

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.
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.
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?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|||Not the way you've written it. You can't correlate inside a nested
query like that.
Here's a possible way to do it (Assuming 'xxx' is your primary key for
the CustomerAddresses table)
-Dave
SELECT c.*, a.*
FROM dbo.Customers c
LEFT JOIN (
SELECT CustomerID, MAX(xxx) AS xxx
FROM dbo.CustomerAddresses
GROUP BY CustomerID) ca
ON ca.CustomerID = c.CustomerID
LEFT JOIN dbo.CustomerAddresses a
ON a.xxx = ca.xxx|||For reaons known only to Microsoft, if the table on the right hand side of a
join is correlated, you have to write APPLY instead of JOIN. Perhaps MS will
one day follow SQL-99. In the meantime, just swear (whilst the kids aren't
around).
OTOH, since TOP is an MS extension, even if MS did support ANSI SQL-99
correlated joins, your query isn't ANSI. Use:
row_number() over (partition by CustomerId order by <something> ) as AddrNo
where <something> is some means of ordering your addresses that allows you
to qualify what you mean by "the first address". You can then add :
and A.AddrNo = 1
to your join's ON clause.
"cmay" <cmay@.walshgroup.com> wrote in message
news:1169932681.524014.158670@.l53g2000cwa.googlegroups.com...
> 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.
> 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.
> 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?
>|||On 28 Jan, 09:53, "Mark Yudkin" <DoNotContac...@.boingboing.org> wrote:
> For reaons known only to Microsoft, if the table on the right hand side of
a
> join is correlated, you have to write APPLY instead of JOIN. Perhaps MS wi
ll
> one day follow SQL-99. In the meantime, just swear (whilst the kids aren't
> around).
>
This is not for "reasons known only to Microsoft". Standard SQL also
doesn't allow correlated queries to reference outside of the derived
table expression unless the LATERAL keyword is used. Microsoft chose
to use APPLY instead of LATERAL but the restriction is exactly the
same one in the case of SQL99 and SQL2003 - namely that a standard
(non-lateral) derived query cannot define itself recursively by using
a correlation outside of its own scope.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I think you're confusing LATERAL and recursive SQL (CTE's, also only partly
supported in SQL 2005) with correlation between table expressions in JOINS.
And in any case, there is no excuse for creating product-specific,
non-standard syntax when following the standard would involve no loss of
functionality. APPLY is not in the SQL-99 (ISO 9074, part 2) standard (let
alone SQL 2003), but the functionality is.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1169994883.390623.313580@.j27g2000cwj.googlegroups.com...
> On 28 Jan, 09:53, "Mark Yudkin" <DoNotContac...@.boingboing.org> wrote:
> This is not for "reasons known only to Microsoft". Standard SQL also
> doesn't allow correlated queries to reference outside of the derived
> table expression unless the LATERAL keyword is used. Microsoft chose
> to use APPLY instead of LATERAL but the restriction is exactly the
> same one in the case of SQL99 and SQL2003 - namely that a standard
> (non-lateral) derived query cannot define itself recursively by using
> a correlation outside of its own scope.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>