Query to reduce a 1-to-n relationship to 1-to-1

[bugs2bugs]

My problem is as follows:
I have a customer table and an address table. A customer can have 0, 1 or many addresses. The query result I'm trying to get produces 1 and only 1 row for each customer, with either 1 address or null's for the appropriate address fields. Here's some simple sql to demonstrate:

create table test_customer (custid char(10), custname char(20))
create table test_address (custid char(10), addrid char(10), city char(20))

insert into test_customer values ('ID1', 'Cust1')
insert into test_customer values ('ID2', 'Cust2')
insert into test_customer values ('ID3', 'Cust3')
insert into test_customer values ('ID4', 'Cust4')

insert into test_address values ('ID1', 'Addr1a', 'Paris')
insert into test_address values ('ID1', 'Addr1b', 'London')
insert into test_address values ('ID2', 'Addr2a', 'New York')
insert into test_address values ('ID3', 'Addr3a', 'Tokyo')

select a.custid, a.custname, b.addrid, b.city
from test_customer a, test_address b
where a.custid *= b.custid

The select produces this result:
ID1 Cust1 Addr1a Paris
ID1 Cust1 Addr1b London
ID2 Cust2 Addr2a New York
ID3 Cust3 Addr3a Tokyo
ID4 Cust4 NULL NULL

The result I'm trying to achieve is:
ID1 Cust1 Addr1a Paris
ID2 Cust2 Addr2a New York
ID3 Cust3 Addr3a Tokyo
ID4 Cust4 NULL NULL

When I try to do a subquery to produce the 1-1 customer/address join, and then do an outer query to add the city, I get the infamous "not permitted outer join" error message:

select a.custid, a.custname, b.addrid, b.city
from test_customer a, test_address b,
(select c.custid, min(d.addrid) sub1addr
from test_customer c, test_address d
where c.custid *= d.custid
group by c.custid) as sub1
where a.custid *= b.custid
and a.custid *= sub1.custid
and b.addrid *= sub1.sub1addr

Output:
Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted.

The subtle change of trying to make the last line an inner join gives me this error:
select a.custid, a.custname, b.addrid, b.city
from test_customer a, test_address b,
(select c.custid, min(d.addrid) sub1addr
from test_customer c, test_address d
where c.custid *= d.custid
group by c.custid) as sub1
where a.custid *= b.custid
and a.custid *= sub1.custid
and b.addrid = sub1.sub1addr

Server: Msg 303, Level 16, State 1, Line 1
The table 'test_address' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

Thanks for any suggestions. Suggestions such as changing the design aren't feasible, as this is a simplified version of a db that's in production.

[Jim Doherty]

My problem is as follows:
I have a customer table and an address table. A customer can have 0, 1 or many addresses. The query result I'm trying to get produces 1 and only 1 row for each customer, with either 1 address or null's for the appropriate address fields. Here's some simple sql to demonstrate:

create table test_customer (custid char(10), custname char(20))
create table test_address (custid char(10), addrid char(10), city char(20))

insert into test_customer values ('ID1', 'Cust1')
insert into test_customer values ('ID2', 'Cust2')
insert into test_customer values ('ID3', 'Cust3')
insert into test_customer values ('ID4', 'Cust4')

insert into test_address values ('ID1', 'Addr1a', 'Paris')
insert into test_address values ('ID1', 'Addr1b', 'London')
insert into test_address values ('ID2', 'Addr2a', 'New York')
insert into test_address values ('ID3', 'Addr3a', 'Tokyo')

select a.custid, a.custname, b.addrid, b.city
from test_customer a, test_address b
where a.custid *= b.custid

The select produces this result:
ID1 Cust1 Addr1a Paris
ID1 Cust1 Addr1b London
ID2 Cust2 Addr2a New York
ID3 Cust3 Addr3a Tokyo
ID4 Cust4 NULL NULL

The result I'm trying to achieve is:
ID1 Cust1 Addr1a Paris
ID2 Cust2 Addr2a New York
ID3 Cust3 Addr3a Tokyo
ID4 Cust4 NULL NULL

When I try to do a subquery to produce the 1-1 customer/address join, and then do an outer query to add the city, I get the infamous "not permitted outer join" error message:

select a.custid, a.custname, b.addrid, b.city
from test_customer a, test_address b,
(select c.custid, min(d.addrid) sub1addr
from test_customer c, test_address d
where c.custid *= d.custid
group by c.custid) as sub1
where a.custid *= b.custid
and a.custid *= sub1.custid
and b.addrid *= sub1.sub1addr

Output:
Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted.

The subtle change of trying to make the last line an inner join gives me this error:
select a.custid, a.custname, b.addrid, b.city
from test_customer a, test_address b,
(select c.custid, min(d.addrid) sub1addr
from test_customer c, test_address d
where c.custid *= d.custid
group by c.custid) as sub1
where a.custid *= b.custid
and a.custid *= sub1.custid
and b.addrid = sub1.sub1addr

Server: Msg 303, Level 16, State 1, Line 1
The table 'test_address' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

Thanks for any suggestions. Suggestions such as changing the design aren't feasible, as this is a simplified version of a db that's in production.

Ok no design change so lets go with what we have here well from what I can see its a question of returning the ONE address for each customer even if they have no address... I've utilised a standard UDF function for that to return a concatenated single return value for the address which you can always parse out to extra columns once you see the return dataset in analyser or opening the view. Here is the script pasted below to create the view and the UDF

--Try this for good measure the UDF function returns the top 1 address
--for the CustID passed in we then SELECT out using the view after that
--the view produces a column of the concatenated address fields essentialthe
--requirements of the single return value the UDF demands
--you can then parse out the elements of the concatenation to extra columns
--I did not know your needs on that so I'll leave that one with you

CREATE FUNCTION UDF_CustomerAddress
( @CustID char(10) )
RETURNS varchar(255)
AS
BEGIN
RETURN (
SELECT TOP 1
COALESCE (LTRIM(RTRIM(Addrid)), '')+' '+ COALESCE (LTRIM(RTRIM(City)), '') as address
FROM test_Address
WHERE CustID = @CustID)
END
Go

CREATE VIEW dbo.vw_CustomersSingleAddress
AS
SELECT custid, custname, dbo.UDF_CustomerAddress(custid) AS [Customer Address]
FROM dbo.test_customer
GO

[bugs2bugs]

Thanks Jim. This works, although I was hoping that the answer wouldn't involve having to parse the string returned by the function (I need to have these values available for loading into another table). Any other ideas out there?

Ok no design change so lets go with what we have here well from what I can see its a question of returning the ONE address for each customer even if they have no address... I've utilised a standard UDF function for that to return a concatenated single return value for the address which you can always parse out to extra columns once you see the return dataset in analyser or opening the view. Here is the script pasted below to create the view and the UDF

--Try this for good measure the UDF function returns the top 1 address
--for the CustID passed in we then SELECT out using the view after that
--the view produces a column of the concatenated address fields essentialthe
--requirements of the single return value the UDF demands
--you can then parse out the elements of the concatenation to extra columns
--I did not know your needs on that so I'll leave that one with you

CREATE FUNCTION UDF_CustomerAddress
( @CustID char(10) )
RETURNS varchar(255)
AS
BEGIN
RETURN (
SELECT TOP 1
COALESCE (LTRIM(RTRIM(Addrid)), '')+' '+ COALESCE (LTRIM(RTRIM(City)), '') as address
FROM test_Address
WHERE CustID = @CustID)
END
Go

CREATE VIEW dbo.vw_CustomersSingleAddress
AS
SELECT custid, custname, dbo.UDF_CustomerAddress(custid) AS [Customer Address]
FROM dbo.test_customer
GO

[azimmer]

Thanks Jim. This works, although I was hoping that the answer wouldn't involve having to parse the string returned by the function (I need to have these values available for loading into another table). Any other ideas out there?

How 'bout this one?

select t.custid, t.custname, addr.addrid, addr.city
from
(
select a.custid, a.custname, min(b.addrid) as minaddrid
from test_customer a left outer join test_address b on a.custid = b.custid
group by a.custid, a.custname
) as t left outer join test_address addr on t.minaddrid=addr.addrid
 

NB: Do not use the WHERE syntax for outer joins -- MSSQL hates it...

[bugs2bugs]

How 'bout this one?

select t.custid, t.custname, addr.addrid, addr.city
from
(
select a.custid, a.custname, min(b.addrid) as minaddrid
from test_customer a left outer join test_address b on a.custid = b.custid
group by a.custid, a.custname
) as t left outer join test_address addr on t.minaddrid=addr.addrid
 

NB: Do not use the WHERE syntax for outer joins -- MSSQL hates it...

Thanks, this is a MUCH more workable solution!