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 userID 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. 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!