Hi All,

I have inherited a SSE 2008 database which includes a members table and an address table. The address table may contain either no address, a home address and/or a workplace address for each member. That is 0, 1 or 2 addresses. Each address is differentiated by an address type field containing either 'H' or 'E' for Home or Work.

Home.Add1 ... Work.Add1 ....
The below query will return a list where the member has two addresses. However, I am unable to return a row where only one address exists.


SELECT A.ad_id,
A.ad_me_no,
A.ad_type,
A.ad_add1 AS 'Home Add1',
A.ad_add2 AS 'Home Add2',
A2.ad_type,
A2.ad_add1 AS 'Work Add1',
A2.ad_add2 AS 'Work Add2'
FROM addresses AS A
LEFT OUTER JOIN addresses AS A2
ON A.ad_me_no = A2.ad_me_no
WHERE
WA2.ad_id > A.ad_id

I am trying to return rows containing both addresses in the format of :
Home.Add1 Home.Add2 . . . Work.Add1,Work.Add2 . . .

Any help appreciated

Yours
Morke

Try What is the difference between ad_add1 and ad_add2?

Hi,
Thanks for the reply. Will try it later on. Add1, Add2 etc are are address lines 1 through 5.

Again Thanks,
Will let you know how it goes.

Hi,
That worked. Owe you a drink if you ever come to Dublin.
Morke

Here are a set of queries to create and populate a set of tables which may help someone else.



use TU -- database
go
drop table addresses
go
drop table Members
go

create table members
(
me_id int identity(1001,1),
me_no varchar(4) not null default '0',
me_name varchar(12) not null default 'X'

)
insert into members (me_name,me_no) values('john 1001','1001')
insert into members (me_name,me_no) values('peter 1002','1002')
insert into members (me_name,me_no) values('john 1003','1003')
insert into members (me_name,me_no) values('mary 1004','1004')
insert into members (me_name,me_no) values('helen 1005','1005')
insert into members (me_name,me_no) values('Mark 1006','1006')
insert into members (me_name,me_no) values('Susan 1007','1007')
insert into members (me_name,me_no) values('Pat 1008','1008')



create table addresses
(
ad_id int identity(100,1),
ad_type char(1) not null default '0',
ad_me_no varchar(4) not null default '0',
ad_add1 varchar(30),
ad_add2 varchar(30)

)
go
insert into dbo.addresses (ad_type,ad_me_no,ad_add1, ad_add2) values('H',1001,'Home Add 1001 Street','Swords')
insert into dbo.addresses (ad_type,ad_me_no,ad_add1, ad_add2) values('E',1001,'Employer Add 1001','Swords')
insert into dbo.addresses (ad_type,ad_me_no,ad_add1, ad_add2) values('H',1002,'Home Add 1002 Street','Swords')
insert into dbo.addresses (ad_type,ad_me_no,ad_add1, ad_add2) values('E',1003,'Employer Add 1003','Swords')

insert into dbo.addresses (ad_type,ad_me_no,ad_add1, ad_add2) values('H',1004,'Home Add 1004 Street','Swords')
insert into dbo.addresses (ad_type,ad_me_no,ad_add1, ad_add2) values('E',1004,'Employer Add 1004','Swords')
insert into dbo.addresses (ad_type,ad_me_no,ad_add1, ad_add2) values('H',1005,'Home Add 10025 Street','Swords')
insert into dbo.addresses (ad_type,ad_me_no,ad_add1, ad_add2) values('E',1005,'Employer Add 1005','Swords')
insert into dbo.addresses (ad_type,ad_me_no,ad_add1, ad_add2) values('E',1006,'Employer Add 1006','Swords')
insert into dbo.addresses (ad_type,ad_me_no,ad_add1, ad_add2) values('E',1008,'Employer Add 1008','Swords')

Not only then
It would have been great if you had included those in your first post.