Remove duplicate records within select query in SQL Server

Last Reply 5 months ago By dharmendr

Posted 5 months ago

Hi! I tryed three days, but couldn't  solve this. Its show duplicate in result. I don't know where my mistake.

 

DECLARE @MANUALSICK AS TABLE (Id INT, NAMEBOL VARCHAR(150), price INT)
INSERT INTO @MANUALSICK VALUES (9,'Тахлили умумии пешоб',5)
INSERT INTO @MANUALSICK VALUES (107,'Муайян намудани  гурухи хун ва  резус омил',14)

DECLARE @SPR_VRCH AS TABLE (KOD_VR INT, NAIM_VR VARCHAR(70))
INSERT INTO @SPR_VRCH VALUES(7,'Холмуродова Гулрухсор')

DECLARE @kassa AS TABLE (Id INT,iddoc int ,name_shah VARCHAR(30),rasid_roh VARCHAR(10), talon_roh INT)
INSERT INTO @kassa VALUES(1,7,'Мадралиева Ш','9,107',7)

DECLARE @IdPer INT
SET @IdPer = 7
SELECT PersonId,
STUFF((SELECT ',' + NAMEBOL
        FROM(
            SELECT k.talon_roh PersonId,LTRIM(RTRIM(m.NAMEBOL))NAMEBOL
            FROM @MANUALSICK m, @kassa k
            WHERE m.Id IN (SELECT CAST(item AS INTEGER) FROM 
        dbo.SplitString(k.rasid_roh, ','))
            AND k.IdDoc = @IdPer
            ) as childTable
        WHERE  mainTable.PersonId = childTable.PersonId
        FOR XML PATH('')), 1, 1, ' ') AS Name,  
naim_vr,
name_shah,
(SELECT SUM(m.Price)
FROM (
    SELECT CAST(Item AS INTEGER) Item
    FROM dbo.SplitString((SELECT TOP 1 rasid_roh
                        FROM @kassa
                        WHERE IdDoc = @IdPer
                        ORDER BY talon_roh DESC), ',')
)t
INNER JOIN @MANUALSICK m ON m.Id = t.Item) Price
FROM(
    SELECT k.talon_roh PersonId,v.NAIM_VR,k.name_shah,m.PRICE
    FROM @kassa k 
    JOIN @SPR_VRCH v ON k.IdDoc = v.KOD_VR, @MANUALSICK m
    WHERE k.talon_roh = (SELECT MAX(talon_roh) FROM @kassa WHERE IdDoc = @IdPer)
    and k.IdDoc = @IdPer
    )AS mainTable
GROUP BY mainTable.NAIM_VR,mainTable.name_shah,mainTable.PersonId,mainTable.PRICE

 

Posted 5 months ago

HI PRA,

Refer below modified query.

SQL

DECLARE @MANUALSICK AS TABLE (Id INT, NAMEBOL VARCHAR(150), price INT)
INSERT INTO @MANUALSICK VALUES (9,'Тахлили умумии пешоб',5)
INSERT INTO @MANUALSICK VALUES (107,'Муайян намудани  гурухи хун ва  резус омил',14)
 
DECLARE @SPR_VRCH AS TABLE (KOD_VR INT, NAIM_VR VARCHAR(70))
INSERT INTO @SPR_VRCH VALUES(7,'Холмуродова Гулрухсор')
 
DECLARE @kassa AS TABLE (Id INT,iddoc int ,name_shah VARCHAR(30),rasid_roh VARCHAR(10), talon_roh INT)
INSERT INTO @kassa VALUES(1,7,'Мадралиева Ш','9,107',7)
 
DECLARE @IdPer INT
SET @IdPer = 7
SELECT DISTINCT PersonId,
STUFF((SELECT ',' + NAMEBOL
        FROM(
            SELECT k.talon_roh PersonId,LTRIM(RTRIM(m.NAMEBOL))NAMEBOL
            FROM @MANUALSICK m, @kassa k
            WHERE m.Id IN (SELECT CAST(item AS INTEGER) FROM
        dbo.SplitString(k.rasid_roh, ','))
            AND k.IdDoc = @IdPer
            ) as childTable
        WHERE  mainTable.PersonId = childTable.PersonId
        FOR XML PATH('')), 1, 1, ' ') AS Name, 
naim_vr,
name_shah,
(SELECT SUM(m.Price)
FROM (
    SELECT CAST(Item AS INTEGER) Item
    FROM dbo.SplitString((SELECT TOP 1 rasid_roh
                        FROM @kassa
                        WHERE IdDoc = @IdPer
                        ORDER BY talon_roh DESC), ',')
)t
INNER JOIN @MANUALSICK m ON m.Id = t.Item) Price
FROM(
    SELECT k.talon_roh PersonId,v.NAIM_VR,k.name_shah,m.PRICE
    FROM @kassa k
    JOIN @SPR_VRCH v ON k.IdDoc = v.KOD_VR, @MANUALSICK m
    WHERE k.talon_roh = (SELECT MAX(talon_roh) FROM @kassa WHERE IdDoc = @IdPer)
    and k.IdDoc = @IdPer
    )AS mainTable
GROUP BY mainTable.NAIM_VR,mainTable.name_shah,mainTable.PersonId,mainTable.PRICE