Sql-server – Wrong results from joining two CTEs

ctesql servert-sql

I've had some help to create a query to solve a problem I had and the solution seemed to be to create two CTE's and then join and query them.

If I run the two CTE's as single queries each table contains the expected data without fail but the join produces less results than it should and duplicates and even rows with three or more duplicates.

;WITH CTE1 AS
(
SELECT FContainerHylla.Name, FContainerPlats.HyllId,
FContainerPlats.x, FContainerPlats.y,
    FContainerPlats.enable, FContainerHylla.Type,
ISNULL((SELECT MIN(fcontainer.id) AS id
FROM FContainer
WHERE (PlatsId = FContainerPlats.id)), 0) AS FcId 
FROM FContainerPlats 
INNER JOIN FContainerHylla ON FContainerPlats.HyllId =

    FContainerHylla.Id
WHERE (FContainerHylla.Type IN (1, 6, 10))
    ),

    CTE2 AS 
    (
SELECT FContainer.id AS SubID, produkter.Produktnamn AS Produktnamn
from FContainer 
INNER JOIN Stämplingar ON FContainer.id = Stämplingar.ID 
INNER JOIN Tempo ON Stämplingar.temponr = Tempo.temponr 
INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr
    ) 

    SELECT CTE1.*, CTE2.Produktnamn
    FROM CTE1
    INNER JOIN CTE2 ON CTE2.SubID = CTE1.FcId
    ORDER BY CTE1.HyllId, CTE1.x, CTE1.y

CTE1 produces a table which is about 2600 rows long and I've verified that it's correct. CTE2 is about 7000 records long and also correct. The column fcontainer.id is a unique key.

As I said when these two tables are joined I get the wrong results. I had expected to get a something similar to the results from CTE1 but with the field produkter.produktnamn added but which I do but instead of the 2500 results from CTE1 I get something around 1600 rows with duplicates.

Create Table For Fcontainer and Stämplingar:

CREATE TABLE [dbo].[FContainer] (
[id]              NUMERIC (18) IDENTITY (1, 1) NOT NULL,
[PlatsId]         NUMERIC (18) CONSTRAINT [DF_FPall_InStoreage] DEFAULT
((0)) NOT NULL,
[Halv]            BIT          NOT NULL,
[FlaggId]         SMALLINT     NOT NULL,
[Kragar]          SMALLINT     NOT NULL,
[TmpFifo]         NUMERIC (18) NULL,
[PackTempoTypeNr] SMALLINT     NULL,
[Invent]          VARCHAR (50) NULL,
CONSTRAINT [PK_FPall] PRIMARY KEY CLUSTERED ([id] ASC) WITH (FILLFACTOR = 
90)
);


CREATE TABLE [dbo].[Stämplingar] (
[Stämplingsnr]   NUMERIC (18) IDENTITY (1, 1) NOT NULL,
[temponr]        NUMERIC (18) NULL,
[Tidpunkt]       DATETIME     NULL,
[antal]          NUMERIC (18) NULL,
[anställd]       CHAR (50)    NULL,
[Bokad]          BIT          CONSTRAINT [DF_Stämplingar_Bokad]
DEFAULT ((0)) NOT NULL,
[Skickad]        BIT          CONSTRAINT [DF_Stämplingar_Skickad]
DEFAULT ((0)) NOT NULL,
[Leveransdatum]  DATETIME     NULL,
[Papp]           NUMERIC (18) NULL,
[daglignr]       NUMERIC (18) NULL,
[ID]             NUMERIC (18) NULL,
[Anställningsnr] NUMERIC (18) NULL,
CONSTRAINT [PK_Stämplingar] PRIMARY KEY NONCLUSTERED ([Stämplingsnr] ASC)
WITH (FILLFACTOR = 90)
);

From help with the comments I thing going with left join seems to be a step in the right way. Now I got about 3100 results instead of 1600. If I just can remove the duplicates I can figure out how to get the produktnamn for those that didn't have it reachable via CTE2.

Some of the relationships might be a bit confusing since they are not very well defined where .id on one table might not be the same thing as .id in another. The query in CTE1 is the original query which I inherited and was trying to expand.

Best Answer

As the discussion in the comments revealed, an fcontainer can be related to many products, due to the 1-to-many relationship between FContainer and Stämplingar.

Try this query, which will choose only one product per fcontainer.

The missing fcontainers are due to the INNER join. Changing it to LEFT join will show all fcontainers, even those not related to any product:

;WITH CTE1 AS
(
    SELECT 
        FContainerHylla.Name, FContainerPlats.HyllId,
        FContainerPlats.x, FContainerPlats.y,
        FContainerPlats.enable, FContainerHylla.Type,
        ISNULL( (SELECT MIN(fcontainer.id) AS id
                 FROM FContainer
                 WHERE (PlatsId = FContainerPlats.id)
                ), 0) AS FcId 
    FROM FContainerPlats 
      INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.Id
    WHERE (FContainerHylla.Type IN (1, 6, 10))
),

    CTE2 AS 
(
    SELECT 
        FContainer.id AS SubID, MIN(produkter.Produktnamn) AS Produktnamn
    FROM FContainer 
      INNER JOIN Stämplingar ON FContainer.id = Stämplingar.ID 
      INNER JOIN Tempo ON Stämplingar.temponr = Tempo.temponr 
      INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr
    GROUP BY 
         FContainer.id
) 

SELECT CTE1.*, CTE2.Produktnamn
FROM CTE1
  LEFT JOIN CTE2 ON CTE2.SubID = CTE1.FcId
ORDER BY CTE1.HyllId, CTE1.x, CTE1.y ;
Related Question