CODEWITHSUNDEEP
sqlsql-serverinner-join
Arturo Martinez
Arturo Martinez

Reputation: 385

Query Inner Duplicates Data

i have a query to select multiple values in 4 Tables however i only need the matches of one inner join on two Tables This is my Query:

SELECT  [dbo].[CHEQUES].Fecha, 
        [dbo].[TURNOS].Descripcion,
        [dbo].[CAPMO].Clave_PDV, 
        [dbo].[CAPMO].Pla AS Platillo_Id, 
        [dbo].[CAPMO].Descripcion, 
        [dbo].[CAPMO].Precio_Neto AS PrecioPlatillo, 
        [dbo].[CAPMO].Can AS CantidadPlatillo

        FROM         [dbo].[CAPMO] 
        INNER JOIN [dbo].[CHEQUES] ON [dbo].[CAPMO].Clave_PDV = [dbo].[CHEQUES].Cla_PDV AND [dbo].[CAPMO].Che = [dbo].[CHEQUES].Che 
        INNER JOIN [dbo].[PLATILLOS] ON [dbo].[CAPMO].Pla = [dbo].[PLATILLOS].Pla 
        INNER JOIN [dbo].[TURNOS] ON [dbo].[CHEQUES].Tur = [dbo].[TURNOS].Tur

This is my 

**[dbo].[CHEQUES]:
|Cla_Pdv    |    Che    |    Fecha    |    Tur    |**
---------------------------------------------------       
|   FUEM    |    4423   |2016-01-01   |     2     |
--------------------------------------------------- 

**[dbo].[TURNO]:
|Clave_Pdv    |    Tur    |    Descripcion    |**
-----------------------------------------------     
|   FUEM      |     1     |      DESAYUNOS    |
-----------------------------------------------
|   FUEM      |     2     |      COMIDAS      |
-----------------------------------------------
|   FUEM      |     3     |      CENAS        |
-----------------------------------------------

**[dbo].[CAPMO]:
|Clave_Pdv    |    Che    |    Com    |    Pla    |    Descripcion    |    Precio_Neto    |    Can    |**
--------------------------------------------------------------------------------------------------------     
|   FUEM      |     4423  |      1    |    184    |    POZ            |    85             |    1    |
------------------------------------------------------------------------------------------------------
|   FUEM      |     4423  |      2    |    184    |    POZ            |    85             |    1    |
------------------------------------------------------------------------------------------------------
|   FUEM      |     4423  |      3    |    184    |    POZ            |    84             |    1    |
------------------------------------------------------------------------------------------------------

**[dbo].[Platillos]:
|    Pla    |    Des    |    Pre   |**
------------------------------------
|   184    |     4423  |      85   |
------------------------------------
|   244    |     4423  |      84   |
------------------------------------

My actual output give me alot of data and it shoul only give me 3 rows of data This is my output Data:

|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  184  |  POZ    |  85 |  1  |
|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  184  |  POZ    |  85 |  1  |
|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  184  |  POZ    |  85 |  1  |
|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  184  |  POZ    |  85 |  1  |
|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  184  |  POZ    |  85 |  1  |
|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  184  |  POZ    |  85 |  1  |
|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  184  |  POZ    |  85 |  1  |
|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  184  |  POZ    |  85 |  1  |
|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  244  |  AGUA   |  84 |  1  |
|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  244  |  AGUA   |  84 |  1  |
|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  244  |  AGUA   |  84 |  1  |
|  2016-01-01   |  COMIDAS  |  4423 |  FUEM |  244  |  AGUA   |  84 |  1  |

But i need my Output to be:

| 2016-01-01 |  COMIDAS |  4423 |  FUEM |  184 | POZ |  85 |    2|
| 2016-01-01 |  COMIDAS |  4423 |  FUEM |  184 | POZ |  84 |    1|

Since there are 3 lines on my [dbo].CAPMO but 1 of them has the same 'Pla' value so i must increment the 'Can' item for the same table '[dbo].[CAPMO]' thats why i need my output like this but in my actual output it seems its multiplying the real value in four columns

What am i doing wrong?

UPDATE

I updated my query to get just exactly the output i want but i had to cut one table out of my query i updated my query to this:

SELECT [dbo].[CHEQUES].Fecha,                         
    [dbo].[CHEQUES].Che,
    [dbo].[CAPMO].Clave_PDV, 
    [dbo].[CAPMO].Pla AS Platillo_Id, 
    [dbo].[CAPMO].Descripcion, 
    [dbo].[CAPMO].Precio_Neto AS PrecioPlatillo, 
    sum( [dbo].[CAPMO].Can) AS CantidadPlatillo

    FROM   [dbo].[CAPMO] 
    Left JOIN [dbo].[Cheques] on [dbo].[Capmo].Che = [dbo].[Cheques].Che

    group by  [dbo].[CHEQUES].Fecha,[dbo].[CHEQUES].Che, [dbo].[CAPMO].Clave_PDV,[dbo].[CAPMO].Pla,[dbo].[CAPMO].Descripcion,[dbo].[2016_01_01_CAPMO].Precio_Neto

This new query makes my output:

| 2016-01-01 |  4423    |  FUEM |  184 | POZ |  85 |    2|
| 2016-01-01 |  4423    |  FUEM |  184 | POZ |  84 |    1|

It gives me the data i need and increases the Quantity correctly but i stil need 1 column that matches the [dbo].[TURNOS], like in this JOIN:

JOIN [dbo].[2016_01_01_TURNOS] ON [dbo].[2016_01_01_CHEQUES].Tur = [dbo].[2016_01_01_TURNOS].Tur

But adding a new join to my query multiply my results and i only need the same output i have but with the column of the data in [dbo].[Turnos], how can i do this?

Upvotes: 0

Views: 80

Answers (1)

jthalliens
jthalliens

Reputation: 544

there should not be duplicated information if you are doing the JOIN in this way :

JOIN [dbo].[2016_01_01_TURNOS] ON [dbo].[2016_01_01_CHEQUES].Tur = [dbo].[2016_01_01_TURNOS].Tur

but I think we can do something this way :

SELECT [dbo].[CHEQUES].Fecha,                         
[dbo].[CHEQUES].Che,
[dbo].[CAPMO].Clave_PDV, 
[dbo].[CAPMO].Pla AS Platillo_Id, 
[dbo].[CAPMO].Descripcion, 
[dbo].[CAPMO].Precio_Neto AS PrecioPlatillo, 
sum( [dbo].[CAPMO].Can) AS CantidadPlatillo,
Dev.Descripcion 
FROM   [dbo].[CHEQUES]   
INNER JOIN
(
    SELECT Cq.Che, t.Descripcion FROM [dbo].[TURNO] t INNER JOIN [dbo].[CHEQUES] Cq ON Cq.Tur = t.tUR 
) AS Dev ON Dev.Che = Cheques.Che 
Left JOIN [dbo].[CAPMO] on [dbo].[Capmo].Che = [dbo].[Cheques].Che

group by  [dbo].[CHEQUES].Fecha,[dbo].[CHEQUES].Che, [dbo].[CAPMO].Clave_PDV,[dbo].[CAPMO].Pla,[dbo].[CAPMO].Descripcion,[dbo].[2016_01_01_CAPMO].Precio_Neto,Dev.Descripcion

let me know if that works otherwise we can figure it out something.

Upvotes: 1

Related Questions