i've trying pull contacts email server's sql database. need pull user, contact, , contacts details.
however, in separate table. i'm running:
select [contacts].[id] ,[users].[loginname] ,[contacts].[jobtitle] ,[contacts].[company] ,[contacts].[webpageaddress] ,[contacts].[firstname] ,[contacts].[lastname] ,[emailaddresses].[address] ,[emailaddresses].[name] ,[addresses].[name] ,[addresses].[address1] ,[addresses].[town] ,[addresses].[county] ,[addresses].[country] ,[addresses].[postcode] ,[phonenumbers].[name] ,[phonenumbers].[number] [workgroupshare].[dbo].[contacts] inner join [workgroupshare].[dbo].[users] on [workgroupshare].[dbo].[contacts].[owner]=[workgroupshare].[dbo].[users].[id] full outer join [workgroupshare].[dbo].[emailaddresses] on [workgroupshare].[dbo].[contacts].[id]= [workgroupshare].[dbo].[emailaddresses].[ownerid] full outer join [workgroupshare].[dbo].[addresses] on [workgroupshare].[dbo].[contacts].[id]= [workgroupshare].[dbo].[addresses].[ownerid] full outer join [workgroupshare].[dbo].[phonenumbers] on [workgroupshare].[dbo].[contacts].[id]= [workgroupshare].[dbo].[phonenumbers].[ownerid] order [contacts].[id] since name field in emailaddress.name contains either email1 or email2 double return each record. , address.name field contains business, home or other triple. , phonenumbers.name contains 4 field quadruple. multiple list of 6000 contacts turns several tens of thousands. can't figure out how write loop or while statement. think have logic can't figure out syntax. appreciated.
if have fixed set of values in each of name fields, can hard-code them columns, 1 row per contact columns representing each 'flavour' of reference data. below (i haven't added 4 phone numbers didn't supply possible values name in case, should able drift):
select [contacts].[id] ,[users].[loginname] ,[contacts].[jobtitle] ,[contacts].[company] ,[contacts].[webpageaddress] ,[contacts].[firstname] ,[contacts].[lastname] ,email1.[address] email1address ,email2.[address] email2address ,address1.[address1] homeaddress1 ,address1.[town] homeaddresstown ,address1.[county] homeaddresscounty ,address1.[country] homeaddresscountry ,address1.[postcode] homeaddresspostcode ,address2.[address1] businessaddress1 ,address2.[town] businessaddresstown ,address2.[county] businessaddresscounty ,address2.[country] businessaddresscountry ,address2.[postcode] businessaddresspostcode ,address3.[address1] otheraddress1 ,address3.[town] otheraddresstown ,address3.[county] otheraddresscounty ,address3.[country] otheraddresscountry ,address3.[postcode] otheraddresspostcode ,[phonenumbers].[name] ,[phonenumbers].[number] [workgroupshare].[dbo].[contacts] inner join [workgroupshare].[dbo].[users] on [workgroupshare].[dbo].[contacts].[owner]=[workgroupshare].[dbo].[users].[id] full outer join [workgroupshare].[dbo].[emailaddresses] email1 on [workgroupshare].[dbo].[contacts].[id]= email1.[ownerid] , email1.[name] = 'email1' full outer join [workgroupshare].[dbo].[emailaddresses] email2 on [workgroupshare].[dbo].[contacts].[id]= email1.[ownerid] , email1.[name] = 'email2' full outer join [workgroupshare].[dbo].[addresses] address1 on [workgroupshare].[dbo].[contacts].[id]= address1.[ownerid] , address1.name = 'home' full outer join [workgroupshare].[dbo].[addresses] address2 on [workgroupshare].[dbo].[contacts].[id]= address2.[ownerid] , address2.name = 'business' full outer join [workgroupshare].[dbo].[addresses] address3 on [workgroupshare].[dbo].[contacts].[id]= address3.[ownerid] , address3.name = 'other' full outer join [workgroupshare].[dbo].[phonenumbers] on [workgroupshare].[dbo].[contacts].[id]= [workgroupshare].[dbo].[phonenumbers].[ownerid] order [contacts].[id] 
Comments
Post a Comment