CODEWITHSUNDEEP
mysqlsql
hamza arhandouri
hamza arhandouri

Reputation: 45

Remove Duplicate rows in left join base on one column

I want to remove the duplicates column with the same id. I want to keep only the first one that shows up.

    SELECT  t.ticketId ,t.userIdOwner , t.ticketCreateDatetime , t.ticketExpectedEndDatetime , t.ticketUpdateDatetime, t.ticketUpdateBy, t.ticketLabel,t.statusTypeIdTicketState,t.statusTypeIdTicketType,t.statusTypeIdTicketModule,
    c.clientLabel, c.clientLogoOnList ,s.taskLabel , s.statusTypeIdTaskCompletion
    FROM ticket AS t
    INNER JOIN client AS c
    ON c.clientId = t.clientId
     LEFT  JOIN task AS s
    ON s.ticketId = t.ticketId  AND  s.statusTypeIdTaskCompletion = (SELECT statusTypeId FROM statusType WHERE statusTypeCode = 'waitingTask' AND statusTypeTargetTable = 'statusTypeIdTaskCompletion'  )
    
    WHERE 1=1
    AND t.ticketDeleteDatetime IS NULL

Here's my screen of results

enter image description here

Edit Image query all results

enter image description here

Edit Image of task structure table :

enter image description here

Upvotes: 0

Views: 79

Answers (1)

MtwStark
MtwStark

Reputation: 4048

You probably have more tasks for each ticket with same statusType

so, if you don't care about different tasks, you can simply add a DISTINCT clause in your SELECT

SELECT DISTINCT
    t.ticketId ,t.userIdOwner , t.ticketCreateDatetime , t.ticketExpectedEndDatetime , 
    t.ticketUpdateDatetime, t.ticketUpdateBy, t.ticketLabel,t.statusTypeIdTicketState,
    t.statusTypeIdTicketType,t.statusTypeIdTicketModule,
    c.clientLabel, c.clientLogoOnList ,s.taskLabel , s.statusTypeIdTaskCompletion
FROM ticket AS t
INNER JOIN client AS c ON c.clientId = t.clientId
LEFT  JOIN task AS s ON s.ticketId = t.ticketId  AND  s.statusTypeIdTaskCompletion = (
    SELECT statusTypeId 
    FROM statusType 
    WHERE statusTypeCode = 'waitingTask' 
    AND statusTypeTargetTable = 'statusTypeIdTaskCompletion'  
)
WHERE 1=1
AND t.ticketDeleteDatetime IS NULL

ok, after your edit, now we can see, that the problem is that more than one task is eligible for the ticket.

Unfortunaterly "the first that show up" is not a valid SQL statement (because the same query could give different results depending on different circumnstances).

So you have to decide wich one to keep, you need to decide a rule, a logic to keep one of the rows.

If you don't need the task label you can simply strip off that column and use the DISTINCT.

If you want to show a task label but don't mind which one, you can use MAX(s.taskLabel) and group by on all other columns.

If you want to keep the last task for that ticket you should provide informations about cronological order (a datetime column or an autoincrement column)

Example with MAX(taskLabel):

SELECT 
    t.ticketId,
    t.userIdOwner, 
    t.ticketCreateDatetime, 
    t.ticketExpectedEndDatetime, 
    t.ticketUpdateDatetime, 
    t.ticketUpdateBy, 
    t.ticketLabel,
    t.statusTypeIdTicketState,
    t.statusTypeIdTicketType,
    t.statusTypeIdTicketModule,
    c.clientLabel, 
    c.clientLogoOnList,
    s.taskLabel, 
    s.statusTypeIdTaskCompletion

FROM ticket AS t
INNER JOIN client AS c ON c.clientId = t.clientId
LEFT JOIN (
    SELECT s.ticketId, MAX(s.taskLabel) AS taskLabel
    FROM task AS s 
    WHERE s.statusTypeIdTaskCompletion = (
        SELECT statusTypeId 
        FROM statusType 
        WHERE statusTypeCode = 'waitingTask' 
        AND statusTypeTargetTable = 'statusTypeIdTaskCompletion'  
    )
    GROUP BY s.ticketId
) S ON s.ticketId = t.ticketId 
WHERE 1=1
AND t.ticketDeleteDatetime IS NULL

Upvotes: 1

Related Questions