Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    21

    Unanswered: Showing Second or Null record

    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
    Last edited by morke; 05-30-11 at 06:54. Reason: Signature

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    SELECT M.Name,
    	M.me_no
    	H.ad_id,
    	H.ad_add1 AS 'Home Add1',
    	H.ad_add2 AS 'Home Add2',
    	W.ad_id,
    	W.ad_add1 AS 'Work Add1',
    	W.ad_add2 AS 'Work Add2'
    FROM members as M
    	LEFT OUTER JOIN addresses AS H ON
    		M.me_no = H.ad_me_no AND
    		H.ad_type = 'H'
    	LEFT OUTER JOIN addresses AS W ON
    		M.me_no = W.ad_me_no AND
    		W.ad_type = 'E'
    What is the difference between ad_add1 and ad_add2?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2009
    Posts
    21
    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.

  4. #4
    Join Date
    Jul 2009
    Posts
    21

    Include null rows

    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')

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Owe you a drink if you ever come to Dublin.
    Not only then
    Here are a set of queries to create and populate a set of tables which may help someone else.
    It would have been great if you had included those in your first post.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •