Ideas Needed For This Sproc

Hi,

I want to write a sproc whose main table is Order and it inner joins another table called OrderShipTo with a one to many relationship so the way i have written the first query is as follows

 SELECT
o.OrderId,
o.OrderDate,
o.PlanId,
o.CreatedByUserId,
o.Quantity,
o.BillToCompany,
o.BillToContact,
o.BillToAddress,
o.BillToCity,
o.BillToState,
o.BillToZip,
o.BillToEmail,
o.BillToPhone,
o.ShipToIsBillTo,
o.BookTypeId,
o.RequiredDeliveryDate,
o.SpecialInstructions,
o.ApprovedBy,
o.ApprovedByPhone,
o.ApprovedByEmail,
os.ShipToId,
os.ShipTypeId,
os.ShipToCompany,
os.ShipToContact,
os.ShipToAddress,
os.ShipToAddress1,
os.ShipToCity,
os.ShipToState,
os.ShipToZip,
os.ShipToEmail,
os.ShipToPhone,
os.Quantity
FROM
[Order] o
Inner Join OrderShipTo os on o.OrderId = os.OrderId
WHERE
o.OrderId = @OrderId

 So suppose if i give an OrderId of 15 In the OrderShipTo table they may be 3 Or 2 or none entries for this order in the Ordership to Table So if there is 3 entries in the OrderShip i will get 3 rows of data which is correct... But i just want o.Columns to appear once and i want n rows for the os.Columns to appear.

So in Order To acheive i wrote this query DECLARE @OrderId int
SET @OrderId = 311
SELECT
o.OrderId,
o.OrderDate,
o.PlanId,
o.CreatedByUserId,
o.Quantity,
o.BillToCompany,
o.BillToContact,
o.BillToAddress,
o.BillToCity,
o.BillToState,
o.BillToZip,
o.BillToEmail,
o.BillToPhone,
o.ShipToIsBillTo,
o.BookTypeId,
o.RequiredDeliveryDate,
o.SpecialInstructions,
o.ApprovedBy,
o.ApprovedByPhone,
o.ApprovedByEmail
FROM
[Order] o
WHERE
o.OrderId = @OrderId
EXEC usp_OrderShipToLoadByOrderId @OrderId

 

 

But at the end i want all the rows o.Columns + the rows from os.columns... I also tried giving a union of both the tables but that didnt work of type mismatch..

How can i acheive that???

 Regards

Karen