TSQL - How to join 1..* from multiple tables in one resultset?

Question

A location table record has two address id's - mailing and business addressID that refer to an address table.

Thus, the address table will contain up to two records for a given addressID.

Given a location ID, I need an sproc to return all tbl_Location fields, and all tbl_Address fields in one resultset:

            LocationID INT,
            ClientID INT,
            LocationName NVARCHAR(50),
            LocationDescription NVARCHAR(50),
            MailingAddressID INT,
            BillingAddressID INT,
            MAddress1 NVARCHAR(255),
            MAddress2 NVARCHAR(255),
            MCity NVARCHAR(50),
            MState NVARCHAR(50),
            MZip NVARCHAR(10),
            MCountry CHAR(3),
            BAddress1 NVARCHAR(255),
            BAddress2 NVARCHAR(255),
            BCity NVARCHAR(50),
            BState NVARCHAR(50),
            BZip NVARCHAR(10),
            BCountry CHAR(3)

I've started by creating a temp table with the required fields, but am a bit stuck on how to accomplish this.

I could do sub-selects for each of the required address fields, but seems a bit messy.

I've already got a table-valued-function that accepts an address ID, and returns all fields for that ID, but not sure how to integrate it into my required result.

Off hand, it looks like 3 selects to create this table - 1: Location, 2: Mailing address, 3: Billing address.

What I'd like to do is just create a view and use that.

Any assistance would be helpful.

Thanks.

Answer 1

something along the lines of the following would work:

select L.*, 
a1.Address1 as MAddress1, a1.Address2 as MAddress2,
a2.Address1 as BAddress1, a2.Address2 as BAddress2
from location L
  inner join Address a1 on (a1.AddressId = L.MailingAddressId)
  inner join Address a2 on (a2.AddressId = L.BillingAddressId)

I didn't put in all of the fields, but you get the idea.

Note that if either of the address ids could be null, the you might use a left join instead.

Answer 2

If I understand your question correctly you want something like:

SELECT
    L.*,
    MAddress1 = M.Address1,
    MAddress2 = M.Address2,
    MCity = M.City,
    MState = M.State,
    MZip = M.Zip,
    MCountry = M.Country
    BAddress1 = B.Address1,
    BAddress2 = B.Address2,
    BCity = B.City,
    BState = B.State,
    BZip = B.Zip,
    BCountry = B.Country
FROM
    tbl_Location L
    INNER JOIN tbl_Address M
        ON L.MailingAddressID = M.MailingAddressID
    INNER JOIN tbl_Address B
        ON L.BillingAddressID = B.BillingAddressID
WHERE
    L.LocationID = @LocationID