2

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.

  • You are not joining on the unique key of cte1 - which seems to be FContainerPlats.id (and not FContainer.id). – ypercubeᵀᴹ Jun 11 '15 at 11:03
  • Also, the cte2 is not joined on its unique key either. This explains the duplicates. – ypercubeᵀᴹ Jun 11 '15 at 11:08
  • Hm, rereading I might be wrong about my first comment. The issue is probably only on the cte2 side. The Stämplingar and FContainer have a 1:n relationship so the FContainer.id AS SubID is not unique (there are more than one Produktnamn for some subIDs). – ypercubeᵀᴹ Jun 11 '15 at 11:11
  • So, the question: what do you want to happen if a fcontainer has many products related? Do you want to show them all or just one? If you want all keep the query you have. If you want just one, you'll need to use MIN(produkter.Produktnamn) AS Produktnamn or another aggregate in the cte2 and also GROUP BY FContainer.id – ypercubeᵀᴹ Jun 11 '15 at 11:17
  • 1
    If you expected to get all rows from CTE1 plus matches from CTE2, then you should probably perform a LEFT OUTER JOIN rather than an INNER JOIN (between the CTEs). – Andriy M Jun 11 '15 at 11:17
2

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 ;
| improve this answer | |
  • Thanks for the help, I appreciate it. Now I finally move on and work on how to find the names on those where I didn't got one. Thanks for your time. – Yo444 Jun 11 '15 at 13:33
  • I'd start with FContainer.PlatsID which can have 0 values and the ISNULL() in the first cte for that. It seems that for some FContainerPlats.id there is not any related FContainer row. – ypercubeᵀᴹ Jun 11 '15 at 13:42
  • I'm going to work on it tomorrow but the problem is in CTE2 more likely. For some of the fcontainers.id in CTE1 there are no records in stämplingar.id that matches so they can't reach produkter.namn that way. I had something similar recently but there is another way to reach produkter.namn that should work for both cases but I can't remember it right now and my work day is soon over. But this is something I'll probably should be able to do myself. Again thanks for your help. – Yo444 Jun 11 '15 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.