CODEWITHSUNDEEP
mysqlsql
user2573004
user2573004

Reputation: 21

SQL Statement to pull records from multiple tables

I've trying to pull contacts from my email server's SQL database. Need to pull the User, the Contact, and all the contacts details.

However, everything is in a separate table. This is what 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]


FROM [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 by [Contacts].[ID]

Since the Name field in the EmailAddress.Name contains either Email1 or Email2 I get a double return for each record. And the Address.Name field contains business, home or other I get a triple. and the PhoneNumbers.Name contains 4 field I get quadruple. Then you multiple that together an a list of 6000 contacts turns into several tens of thousands. Can't figure out how to write a loop or while statement. I think I have the logic just can't figure out the syntax. Any help would be greatly appreciated.

Upvotes: 2

Views: 173

Answers (1)

StevieG
StevieG

Reputation: 8709

If you have a fixed set of values in each of the Name fields, then you can hard-code them into columns, so you get 1 row per contact with columns representing each 'flavour' of reference data. Something like below (I haven't added it for the 4 phone numbers as you didn't supply the possible values for Name in that case, but you should be able to get the drift):

SELECT [Contacts].[ID]
  ,[Users].[LoginName]
  ,[Contacts].[JobTitle]
  ,[Contacts].[Company]
  ,[Contacts].[WebPageAddress]
  ,[Contacts].[FirstName]
  ,[Contacts].[LastName]
  ,email1.[Address] as email1Address
  ,email2.[Address] as email2Address
  ,Address1.[Address1] as HomeAddress1
  ,Address1.[Town] as HomeAddressTown
  ,Address1.[County] as HomeAddressCounty
  ,Address1.[Country] as HomeAddressCountry
  ,Address1.[Postcode] as HomeAddressPostcode
  ,Address2.[Address1] as BusinessAddress1
  ,Address2.[Town] as BusinessAddressTown
  ,Address2.[County] as BusinessAddressCounty
  ,Address2.[Country] as BusinessAddressCountry
  ,Address2.[Postcode] as BusinessAddressPostcode
  ,Address3.[Address1] as OtherAddress1
  ,Address3.[Town] as OtherAddressTown
  ,Address3.[County] as OtherAddressCounty
  ,Address3.[Country] as OtherAddressCountry
  ,Address3.[Postcode] as OtherAddressPostcode
  ,[PhoneNumbers].[Name] 
  ,[PhoneNumbers].[Number]
FROM [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] AND email1.[Name] = 'Email1'
  FULL OUTER JOIN [WorkgroupShare].[dbo].[EmailAddresses] email2
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= email1.[OwnerID] AND email1.[Name] = 'Email2'
  FULL OUTER JOIN [WorkgroupShare].[dbo].[Addresses] Address1
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= Address1.[OwnerID] AND Address1.Name = 'Home'
  FULL OUTER JOIN [WorkgroupShare].[dbo].[Addresses] Address2
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= Address2.[OwnerID] AND Address2.Name = 'Business'
  FULL OUTER JOIN [WorkgroupShare].[dbo].[Addresses] Address3
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= Address3.[OwnerID] AND Address3.Name = 'Other'
  FULL OUTER JOIN [WorkgroupShare].[dbo].[PhoneNumbers]
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= [WorkgroupShare].[dbo].[PhoneNumbers].[OwnerID]
  order by [Contacts].[ID]

Upvotes: 1

Related Questions