sql-server - LEFT JOIN 与 FOR XML AUTO, ELEMENTS

Question

我有3张 table

Master {MasterID, Desc},
Detail {MasterID, DetailID, ItemID},
Items  {ItemID, ItemDesc})

我想选择 Master、Detail(作为 Master 中的 SubElement)、Item(在 Detail 的相同元素中)

SELECT Master.MasterID, Master.Desc, Detail.DetailID, Detail.ItemID, Items.ItemDesc
FROM Master
    LEFT JOIN Detail 
        LEFT JOIN Items 
            ON Detail.ItemID = Items.ItemID 
    ON Master.MasterID = Detail.MasterID
FOR XML AUTO, ELEMENTS

它给出了以下结果:

<Master>
 <MasterID>1</MasterID>
 <Desc>Master1</Desc>
 <Detail>
  <DetailID>1</DetailID>
  <ItemID>1</ItemID>
  <Items><ItemDesc>ItemDesc1</ItemDesc></Items>
 </Detail>
</Master>

但我的目标是

<Master>
 <MasterID>1</MasterID>
 <Desc>Master1</Desc>
 <Detail>
  <DetailID>1</DetailID>
  <ItemID>1</ItemID>
  <ItemDesc>ItemDesc1</ItemDesc>
 </Detail>
</Master>'

我怎样才能用最佳实践方式做到这一点？

Answer 1

从单个查询返回详细信息和项目描述，如下所示:

SELECT 
    Master.MasterID, 
    Master.Desc, 
    Detail.DetailID, 
    Detail.ItemID, 
    Detail.ItemDesc 
FROM 
    Master 
    LEFT JOIN (
        select
            d.MasterID,
            d.DetailID,
            d.ItemID,
            i.ItemDesc
        from
            Detail d
            left join items i on
                d.itemid = i.itemid
    ) Detail ON
        Master.MasterID = Detail.MasterID
FOR XML AUTO, ELEMENTS