LEFT JOIN Using distinct?

left join distinct mysql
left join without duplicates from right table
remove duplicates in left join
how to use distinct in join query in mysql
left outer join (select distinct)
sql join on non unique column
sql remove duplicate columns after join
left join causing duplicates

Originally, I have 15 employees on my table. Two of them have the same LastName.

If I leave the E.MAT_EMP selection as it is, it works fine and 15 employees will get selected.

If I remove it though (because I don't want it to be shown) except for the row count, Firstname, Lastname and month days. It returns to me 14 rows instead of 15. After checking, It's apparently one of the rows where I have the same LastName/FirstName.

@StartDate date,
@EndDate date

as
begin

Declare @D1 date = @StartDate
Declare @D2 date = @EndDate

Declare @Cols varchar(max) = (Select Stuff((Select ',[' +cast(N as varchar(25))+']' From (Select Top (DateDiff(DAY,@D1,@D2)+1) N=Row_Number() Over (Order By (Select Null)) From  master..spt_values n1) A For XML Path ('')),1,1,'') )
Declare @SQL varchar(max) = '
Declare @D1 date = '''+cast(@D1 as varchar(50))+'''
Declare @D2 date = '''+cast(@D2 as varchar(50))+'''

Select ROW_NUMBER() OVER (ORDER BY LastName ASC) as [N°],*
From  ( 
        Select E.MAT_EMP, NOM_EMP as [LastName],PRENOM_EMP as [FirstName],
              Item      = day(d)
              ,Value     = 
              case when (D between DEBUT_DRC and FIN_DRC) and STATUS_DRC = ''Accepté'' then ''RC'' 
              when (D between DEBUT_DAB and FIN_DAB) and STATUS_DAB = ''Accepté'' then ''ABS'' 
              when (D between DC_DEBUT and DC_FIN) and STATUS_DC = ''Accepté'' then DCon.CODE_TYPE_CONGE
              else '''' 

              end
         From 
         DEMANDE_RECUPERATION DC RIGHT JOIN EMPLOYE E 
         ON DC.MAT_EMP = E.MAT_EMP 

         LEFT JOIN DEMANDE_ABSENCE ABS
         ON E.MAT_EMP = ABS.MAT_EMP 

         LEFT JOIN DEMANDE_CONGE DCon 
         ON E.MAT_EMP = DCon.MAT_EMP 

         Cross Join (
                        Select Top (DateDiff(DAY,@D1,@D2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1) From  master..spt_values n1
                    ) B
       ) src
 Pivot (max(value) for Item in ('+@Cols+') ) pvt
'
Exec(@SQL)

The output is as follows:

My goal is to simply remove that column while keeping the 15 rows.

Replace the * with the column names you want to show, and don't remove it from the subquery.

    Select ROW_NUMBER() OVER (ORDER BY LastName ASC) as [N°], [LastName], [FirstName] /*other needed columns here*/
From  ( 
        Select E.MAT_EMP, NOM_EMP as [LastName],PRENOM_EMP as [FirstName],
              Item      = day(d)
              ,Value     = 
              case when (D between DEBUT_DRC and FIN_DRC) and STATUS_DRC = ''Accepté'' then ''RC'' 
              when (D between DEBUT_DAB and FIN_DAB) and STATUS_DAB = ''Accepté'' then ''ABS'' 
              when (D between DC_DEBUT and DC_FIN) and STATUS_DC = ''Accepté'' then DCon.CODE_TYPE_CONGE
              else '''' 

              end
         From 
         DEMANDE_RECUPERATION DC RIGHT JOIN EMPLOYE E 
         ON DC.MAT_EMP = E.MAT_EMP 

         LEFT JOIN DEMANDE_ABSENCE ABS
         ON E.MAT_EMP = ABS.MAT_EMP 

         LEFT JOIN DEMANDE_CONGE DCon 
         ON E.MAT_EMP = DCon.MAT_EMP 

         Cross Join (
                        Select Top (DateDiff(DAY,@D1,@D2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1) From  master..spt_values n1
                    ) B
       ) src
 Pivot (max(value) for Item in ('+@Cols+') ) pvt

SELECT DISTINCT with LEFT JOIN, ORDERed BY in t-SQL, When you narrow it down individual id's, you create the possibility that each id might have more than one dtOut associated with it. When that  Re: Left join with distinct. Hi Anosh, When you use disting with join or concatenate in QlikView it works for all tables which are joined or concatenated. Istead of this In SQL it work only for table where you use it. In order to prove it you can try to do concatenate in qlikview using disting for one table.

At the final query on SELECT statement specify columns' name that you want to show: [N°], [LastName], [FirstName] because the * indicates that you want to show all the columns that you have select previously.

Removing Duplicates from a LEFT JOIN – SQLServerCentral, The LEFT JOIN I'm using is displaying duplicates of the records in A (if a Fields in table B are different for each record, so running DISTINCT  Exists always turns up Left Semi Join in the executionplan for me; while you describe a 'nested loops'. Do you have a scenario in which you can reproduce this? Edited by Sebastian vd Putten Wednesday, July 25, 2012 8:06 AM typo

Check these 2 pivot examples to see how PIVOT implicitly groups by all non-aggregate columns:

IF OBJECT_ID('tempdb..#EmployeeData') IS NOT NULL
    DROP TABLE #EmployeeData

CREATE TABLE #EmployeeData (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Type CHAR(1),
    Number INT)

INSERT INTO #EmployeeData (
    EmployeeID,
    EmployeeName,
    Type,
    Number)
VALUES
    (1, 'Mark', 'A', 10),
    (1, 'Mark', 'A', 10),
    (1, 'Mark', 'B', 15),
    (1, 'Mark', 'C', 5),
    (2, 'Leonard', 'A', 10),
    (2, 'Leonard', 'C', 45),
    (3, 'Mary', 'B', 10),
    (3, 'Mary', 'C', 15),
    (3, 'Mary', 'C', 25),
    (3, 'Mary', 'C', 5),
    (4, 'Mary', 'A', 25), -- Diferent ID!
    (4, 'Mary', 'A', 15),
    (4, 'Mary', 'C', 20),
    (4, 'Mary', 'C', 35)

First pivot:

-- Pivot Groups by EmployeeID + EmployeeName (all non-aggregate columns)
SELECT
    P.*
FROM
    #EmployeeData AS S
    PIVOT (
        SUM(S.Number) FOR S.Type IN ([A], [B], [C])
    ) AS P

Result:

EmployeeID  EmployeeName    A       B       C
2           Leonard         10      NULL    45
1           Mark            20      15      5
3           Mary            NULL    10      45
4           Mary            40      NULL    55

Second pivot:

-- Pivot Groups by EmployeeName (only)
;WITH NoEmployeeName AS
(
    SELECT
        E.EmployeeName,
        E.Type,
        E.Number
    FROM
        #EmployeeData AS E
)
SELECT
    P.*
FROM
    NoEmployeeName AS S
    PIVOT (
        SUM(S.Number) FOR S.Type IN ([A], [B], [C])
    ) AS P

Result:

EmployeeName    A   B       C
Leonard         10  NULL    45
Mark            20  15      5
Mary            40  10      100

When pivoting, the values of the rows that you are pivoting will be converted to the column names. You can reference these column names on your SELECT (you can avoid using *):

SELECT
    P.EmployeeID,
    P.EmployeeName,

    P.A, -- Pivot values as column names
    P.B,
    P.C
FROM
    #EmployeeData AS S
    PIVOT (
        SUM(S.Number) FOR S.Type IN ([A], [B], [C])
    ) AS P

What you need to do is to pivot using your E.MAT_EMP (has to be on the subquery) so your employee data doesn't get grouped by, and then don't list it on the SELECT.

Declare @D1 date = CONVERT(DATE, GETDATE() - 5)
Declare @D2 date = CONVERT(DATE, GETDATE())

Declare @Cols varchar(max) = (Select Stuff((Select ',[' +cast(N as varchar(25))+']' From (Select Top (DateDiff(DAY,@D1,@D2)+1) N=Row_Number() Over (Order By (Select Null)) From  master..spt_values n1) A For XML Path ('')),1,1,'') )

Declare @SQL varchar(max) = '
Declare @D1 date = '''+cast(@D1 as varchar(50))+'''
Declare @D2 date = '''+cast(@D2 as varchar(50))+'''

Select 
    ROW_NUMBER() OVER (ORDER BY LastName ASC) as [N°],

    pvt.LastName, -- Grouping columns of pivot operator (without MAT_EMP)
    pvt.FirstName,

    -- New columns result of pivot:
    ' + @Cols + ' 

From  ( 
    Select 
        E.MAT_EMP, 
        NOM_EMP as [LastName],
        PRENOM_EMP as [FirstName],
        Item = day(d),
        Value = case 
            when (D between DEBUT_DRC and FIN_DRC) and STATUS_DRC = ''Accepté'' then ''RC'' 
            when (D between DEBUT_DAB and FIN_DAB) and STATUS_DAB = ''Accepté'' then ''ABS'' 
            when (D between DC_DEBUT and DC_FIN) and STATUS_DC = ''Accepté'' then DCon.CODE_TYPE_CONGE
            else '''' end
    From 
        DEMANDE_RECUPERATION DC 
        RIGHT JOIN EMPLOYE E ON DC.MAT_EMP = E.MAT_EMP 
        LEFT JOIN DEMANDE_ABSENCE ABS ON E.MAT_EMP = ABS.MAT_EMP 
        LEFT JOIN DEMANDE_CONGE DCon ON E.MAT_EMP = DCon.MAT_EMP 
        Cross Join (
            Select Top (DateDiff(DAY,@D1,@D2)+1) 
                D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1) 
            From  
                master..spt_values n1) B
       ) src
    Pivot (
        max(value) for Item in ('+@Cols+') 
    ) pvt
'

PRINT(@SQL)

-- EXEC (@SQL)

If printed, this is the result (for some example date values I hard-coded):

Declare @D1 date = '2018-12-16'
Declare @D2 date = '2018-12-21'

Select 
    ROW_NUMBER() OVER (ORDER BY LastName ASC) as [N°],

    pvt.LastName, -- Grouping columns of pivot operator (without MAT_EMP)
    pvt.FirstName,

    -- New columns result of pivot:
    [1],[2],[3],[4],[5],[6] 

From  ( 
    Select 
        E.MAT_EMP, 
        NOM_EMP as [LastName],
        PRENOM_EMP as [FirstName],
        Item = day(d),
        Value = case 
            when (D between DEBUT_DRC and FIN_DRC) and STATUS_DRC = 'Accepté' then 'RC' 
            when (D between DEBUT_DAB and FIN_DAB) and STATUS_DAB = 'Accepté' then 'ABS' 
            when (D between DC_DEBUT and DC_FIN) and STATUS_DC = 'Accepté' then DCon.CODE_TYPE_CONGE
            else '' end
    From 
        DEMANDE_RECUPERATION DC 
        RIGHT JOIN EMPLOYE E ON DC.MAT_EMP = E.MAT_EMP 
        LEFT JOIN DEMANDE_ABSENCE ABS ON E.MAT_EMP = ABS.MAT_EMP 
        LEFT JOIN DEMANDE_CONGE DCon ON E.MAT_EMP = DCon.MAT_EMP 
        Cross Join (
            Select Top (DateDiff(DAY,@D1,@D2)+1) 
                D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1) 
            From  
                master..spt_values n1) B
       ) src
    Pivot (
        max(value) for Item in ([1],[2],[3],[4],[5],[6]) 
    ) pvt

More dangerous subtleties of JOINs in SQL, values based on LastName column. You do this with a Row_Number() partitioned by the LastName, and sorted by the FirstName. below is my insert query. insert into /*+ append*/ temp_custparam(custno, rating) select distinct q.custno, nvl(((nvl(p.rating,0) * '10.0')/100),0) as rating from tb_accounts q left join tb_custparam p on p.text_param in (select distinct prdcd from tb_accounts) and p.tablename='tb_accounts' and p.columnname='prdcd';

SQL Server - INNER JOIN WITH DISTINCT, on the provided columns. You can do this using generic SQL with group by : SELECT C. You can use CTE to get the distinct values of the second table, and then join that with the first table. You also need to get the distinct values based on LastName column. You do this with a Row_Number() partitioned by the LastName, and sorted by the FirstName. Here's the code

DISTINCT - Caché & Ensemble 2018.1.3, Instead of using LEFT JOIN with acknowledgements.incident_id IS NOT NULL , we can simply use a plain old INNER JOIN . Unfortunately,  Introduction to SQL Server LEFT JOIN clause. The LEFT JOIN clause allows you to query data from multiple tables. It returns all rows from the left table and the matching rows from the right table. If no matching rows found in the right table, NULL are used. The following illustrates how to join two tables T1 and T2 using the LEFT JOIN clause:

Left Join without duplicate rows from left table, I found a few fields but they are not consistent with all the recods. I will have to keep looking. Thanks David and Corwin. If you have more ideas,  In SQL, we use the following syntax to join table A with table B. SELECT A.n FROM A LEFT JOIN B ON B.n = A.n; The LEFT JOIN clause appears after the FROM clause. The condition that follows the ON keyword is called the join condition B.n = A.n. SQL LEFT JOIN examples SQL LEFT JOIN two tables examples. Let’s take a look at the countries and

Comments
  • I didn't inspect closely but I believe that your pivot function is implicilty grouping by all non-aggregate columns (it always does this) and you are "losing" a row because of the aggregation.
  • If you remove E.MAT_EMP from the "src" sub-query then the PIVOT will only group by NOM_EMP & PRENOM_EMP. So if it got more than 1 MAT_EMP for the same last name & first name, then it can be expected to get less rows. Anyway, what would happens if the [N°],* in the outer query is replaced by [N°], [LastName], [FirstName], '+@Cols+'
  • @EzLo What's the solution in that case?
  • @LukStorms I think it should be ''+@Cols+'' because it's XML. But that's not working, I get the columns but for the one that represent the days, I only get one with +@Cols+ inside it.
  • That's working but how I'm supposed to show the column for the days?
  • Name the columns on your subquery and then use these names on the main select.
  • Thank you but as said above and below, How can I show the other columns?
  • Which ones ? You just said 'My goal is to simply remove that column while keeping the 15 rows' If you refer to [Item] and [Value], try to put them after the others in the outer query like this [N°], [LastName], [FirstName], pvt.[Item], pvt.[Value]
  • May be you try letting the Select statement of the 'second' query as you have it and make a third outer query where you specefics your final columns' names.