Post reply

One to Many select statement to be returned as one row of data

  • March 7, 2011 at 7:42 am

    #234770

    Hi,

    Hope someone can help me - I'm trying to bring a one to many result set in to one row.

    We are maintaining customers in the table "Customers" and their many addresses in the table "Addresses".

    In the customer table it refers to 2 addresses (C.billingaddressID, c.shippingaddressID).

    I'd like one row of data giving me everything selected below from the customer table and then the billing address details and the shipping address details.

    Here is the sql giving the one to many result.

    SELECT c.CustomerID,c.RegisterDate, c.Email, c.FirstName, c.LastName, c.Phone,

    c.AffiliateID, c.Referrer, c.OkToEmail, c.BillingEqualsShipping, c.BillingAddressID, c.ShippingAddressID,

    c.CreatedOn, c.VATSetting, c.VATRegistrationID, c.Active,

    a.Company, a.FirstName, a.LastName, a.NickName, a.Address1, a.Address2, a.City, a.State, a.Country, a.Phone, a.email

    FROM [database.co.uk].[dbo].[Customer] c

    inner join [database.co.uk].[dbo].[Address] a

    on c.CustomerID = a.CustomerID

  • March 7, 2011 at 9:45 am

    #1295421

    You could do multiple joins to the address detail table based on address type, something like this:

    SELECT c.CustomerID,c.RegisterDate, c.Email, c.FirstName, c.LastName, c.Phone,

    c.AffiliateID, c.Referrer, c.OkToEmail, c.BillingEqualsShipping, c.BillingAddressID, c.ShippingAddressID,

    c.CreatedOn, c.VATSetting, c.VATRegistrationID, c.Active,

    a1.Company, a1.FirstName, a1.LastName, a1.NickName, a1.Address1, a1.Address2, a1.City, a1.State, a1.Country, a1.Phone, a1.email

    a2.Company, a2.FirstName, a2.LastName, a2.NickName, a2.Address1, a2.Address2, a2.City, a2.State, a2.Country, a2.Phone, a2.email

    FROM [database.co.uk].[dbo].[Customer] c

    left outer join [database.co.uk].[dbo].[Address] a1 on c.CustomerID = a1.CustomerID and c.BillingAddressID = a1.AddressID

    left outer join [database.co.uk].[dbo].[Address] a2 on c.CustomerID = a2.CustomerID and c.ShippingAddressID = a2.AddressID

    _____________________________________________________________________
    - Nate

    @nate_hughes

  • March 7, 2011 at 9:59 am

    #1295436

    The code above is good if you know how many rows you'll get. If not, you might look up PIVOT or cross tab queries to see if they help.

  • March 7, 2011 at 10:30 am

    #1295455

    so the code could produce multiple rows still is there were 3 addresses to one customer?

    I will test it.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply