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

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.

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.

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