I have a table named ListItem and another table ListItemRelationship which holds the many-to-many relationship between the items in the ListItem table.

Example: ListItem table, ListItemGroupID is used to provide one-to-many relationship.

Example ListItemRelationship table this table is to just maintain many-to-many relationship

Here, I am saying Euro is used by multiple countries like Germany and France.

Now, while trying to fetch all the cities in one state, my following query works,

SELECT (
        SELECT
            ListItemID AS [value],
            ListItemCode
        FROM ListItem AS [Item]
        WHERE ListCode = 'City'
            AND (ListItemGroupID = ISNULL(2926, ListItemGroupID) OR ListItemGroupID IS NULL AND 2926 IS NULL)   
        FOR XML RAW
        )

But, to fetch the records from LisItemRelationship, I am not getting the desired result. The query I am trying is

SELECT (
        SELECT
            LIR.ListItemID AS [value],
            ListItemCode
        FROM ListItem AS [Item] INNER JOIN ListItemRelationship AS LIR ON Item.ListItemID = LIR.ListItemParentID
        WHERE ListCode = 'Country'
            AND (LIR.PickListItemParentID = 2925)   
        FOR XML RAW
        )

I am expecting to receive following XML from 2nd query

<xml>
    <row value="2928" ListItemCode="Germany"/>
    <row value="2929" ListItemCode="France"/>
</xml>

What is wrong with my query?

CodePudding user response：

There seem to be a few errors in your column names. Try the following:

SELECT (
        SELECT
            LIR.ListItemID AS [value],
            Item.[Value] as ListItemCode
        FROM ListItem AS [Item]
        INNER JOIN ListItemRelationship AS LIR ON Item.ListItemID = LIR.ListItemID
        WHERE Item.ListItemCode = 'Country'
            AND (LIR.ListItemParentID = 2925)   
        FOR XML RAW
        )