Comma separated value string and compare against other values

  • Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything.

    Here is my query:

    DECLARE @ATID AS VARCHAR(3) = 12
    DECLARE @AssetID AS VARCHAR(7) = 306

    SELECT
    tblXDataA.AssetID AS 'LID',
    tblA.AssetID AS 'AID',
    tblEM.[Custom] AS 'ABC/S XML',
    tblEMXData.[Custom] AS 'XData XML',
    tblEM.[Custom].value('(/ABCS/text())[1]', 'varchar(MAX)') AS 'Parsed XML -> ABCS',
    tblEM.[Custom].value('(/ABC/text())[1]', 'varchar(MAX)') AS 'Parsed XML -> ABC',
    tblEMXData.[Custom].value('(/XData/ABC/text())[1]', 'varchar(MAX)') AS 'Parsed XML -> XData'
    FROM
    Asset AS tblA
    JOIN
    LinkStr AS tblL
    ON tblL.A_AssetID = tblA.AssetID
    JOIN
    Asset AS tblXDataA
    ON tblXDataA.AssetID = tblL.B_AssetID
    JOIN
    EMd AS tblEMXData
    ON tblEMXData.AssetID = tblXDataA.AssetID
    JOIN
    EMd AS tblEM
    ON tblEM.AssetID = tblA.AssetID
    JOIN
    LinkStr AS tbLinkStrB
    ON tblXDataA.AssetID = tbLinkStrB.B_AssetID
    WHERE
    tblXDataA.ATID = @ATID
    AND
    tblA.AssetID = @AssetID;

    All of that above produces a few columns of output but the main columns are the ABCS and XData:

    LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData 
    ------------------------------------------------------------------------------------------------------------
    481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240
    501 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| 05649880-ED43

    The above is correct as-is. There are 2 users and therefore the ABCS has a comma delimiter with both users ABCS. The first user that is added is the main user that all other users go under and therefore it has the same user ID for both [306].

    The users ABCS are:

    User 1 [main] : Y40GB4DL4-48302343420105-FH3J-8798-11240

    User 2 [addition]: 05649880-ED43

    However, If I delete the last user added [05649880-ED43] then ABCS should only have 1 users ABCS in it instead of 2. But this is how its turning out:

    LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData 
    ------------------------------------------------------------------------------------------------------------
    481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240

    So depending on how many users are added to the main user is what I need to check for to make sure those users who get deleted are not left in the ABCS comma string.

    Needing to:

    Get each ABCS comma delimited value and compare it to XData(s)

    So as the example above it would flow like this taken that the database looks like this:

    LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData 
    ------------------------------------------------------------------------------------------------------------
    481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240
    501 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| 05649880-ED43

    Is Y40GB4DL4-48302343420105-FH3J-8798-11240 [ABCS] present in [XData]? answer: true

    Is 05649880-ED43 [ABCS] present in [XData]? answer: true

    Since there are 2 users and 2 rows then that would, of course, return both as TRUE and that would be all.

    Now If the added user gets deleted from the main user then the table would look like this and which is what currently is happening:

    LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData 
    ------------------------------------------------------------------------------------------------------------
    481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240

    Is Y40GB4DL4-48302343420105-FH3J-8798-11240 [ABCS] present in [XData]? answer: true

    Is 05649880-ED43 [ABCS] present in [XData]? answer: false

    Since its true and false then the false users ABCS ID needs to be taken out [DELETED/REMOVED] of the comma separated string. This is what I am unable to find out how to go about doing.

    Help would be great!

  • Couple of ways to solve this.   First, you could do the following.

    1. Use the OUTPUT clause in conjuction with your delete to save the deleted AIDs to  a temporary table.
    2. Get the list of distinct AIDs from the temp table, and rebuild the comma delimited string for each AIDs
    3. Update the remaining rows with the rebuilt string(s) from step 2

    Second, you could just eliminate the [Parsed XML -> ABCS ] column as a persisted column, and just create it on the fly from the data in the various [Parsed -> XData] elements.   Unfortunately this would  mean reading all rows for each AID before presenting the results.

     

     

    There may be other approaches, but this is what immediately comes to mind.

     

     

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • If the delete of the added user is an actual delete from a table, then use an after delete trigger to remove added users from the main row at the time the row is deleted.  You don't want to leave inconsistent data in the table even beyond just this one use of the table.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply