SQL Server JOIN returning multiple rows - need one row on left side-CodePudding

I've Googled and tested extensively. I think I may have brain fade.

Our website has specific landing pages for various products.

The Control table holds the page title and product search information.

Control

ID       Title                Category        Colour
------------------------------------------------------------
1        Blue Postcards       Postcard        Blue
2        Blue Photos          Photo           Blue
3        Yellow Postcards     Postcard        Yellow
4        Postcards            Postcard        null

Products

pk_ProdID       Category         Price
-------------------------------------------
100             Postcard         59
200             Photo            42
300             Postcard         33
400             Photo            97
500             Postcard         78
600             Postcard         48

Each Product has records for zero or more colours it can be sold in.

Products_Colours

fk_ProdID  Colour
-------------------------
100        Blue
100        Yellow
200        Blue
300        Yellow
400        Red
500        Blue
500        Yellow
600        Blue

If the ID of Control is 1, the page should show Blue Postcards.

The following is the desired result:

ProdID         Category         Price
-------------------------------------------
100            Postcard         59
500            Postcard         78
600            Postcard         48

What I am actually getting is:

ProdID         Category         Price
-------------------------------------------
100            Postcard         59
100            Postcard         59
500            Postcard         78
500            Postcard         78
600            Postcard         48

SQL

This stored procedure returns two recordsets for my PHP page to reduce the number of requests to the database.

DECLARE @OrderBy varchar(1)
SET @OrderBy = 'D'
DECLARE @Row int
SET @Row = 1
DECLARE @ControlID int
SET @Control = 1

/* get control info for web page */
SELECT 
c.ID,
c.Title

FROM dbo.Control c

WHERE c.ID = @ControlID;

/* get search criteria */
WITH ControlSearch AS 
(
SELECT 
ID,
Category,
Colour
FROM Control WHERE ID = @ControlID
),

/* get products that match search criteria */
ProductSearch AS 
(
SELECT 
DISTINCT p.ProdID,       
p.Category
Count(1) OVER() As Total_Records,
   
   ROW_NUMBER() OVER(
   ORDER BY 
    CASE WHEN @OrderBy = 'D' THEN p.Price END DESC,
    CASE WHEN @OrderBy = 'U' THEN p.Price END ASC
    ) As RowNum       
       
   FROM dbo.Products p
   JOIN ControlSearch l ON l.ID = @ControlID
   LEFT JOIN dbo.Products_Colours co ON p.ProdID = co.ProdID
   WHERE
  
   (p.Category = l.Category)
   
   AND       
  
   (co.Colour = l.Colour OR l.Colour is null)
)   

/* return recordset */
SELECT * FROM ProductSearch WHERE RowNum >= @Row AND RowNum < (@Row   50)

I display the total number of matching records to the user, and output row numbers so I can paginate by 50 products.

Why does this return multiple Products according to how many colours it has?

CodePudding user response：

Bit of a stab in the dark, but perhaps what you really want here is an EXISTS:

DECLARE @ControlID int
SET @ControlID = 1;

SELECT *
FROM dbo.Products P
WHERE EXISTS (SELECT 1
              FROM dbo.Control C
                   LEFT JOIN dbo.Products_Colours PC ON C.Colour = PC.Colour
              WHERE C.Category = P.Category
                AND PC.fk_ProdID = P.pk_ProdID
                AND (PC.Colour IS NOT NULL OR C.Colour IS NULL)
                AND C.ID = @ControlID);

CodePudding user response：

Looks like you need something like this

SELECT
  p.ProdID,       
  p.Category,
  COUNT(*) OVER () AS Total_Records,
  CASE WHEN @OrderBy = 'U'
    THEN COUNT(*) OVER () - ROW_NUMBER() OVER (ORDER BY p.Price)   1
    ELSE ROW_NUMBER() OVER (ORDER BY p.Price) END
    AS RowNum   
FROM dbo.Products P
JOIN dbo.Control C ON C.ID = @ControlID  -- single-row cross join
WHERE C.Category = P.Category
  AND (
    C.Colour IS NULL OR EXISTS (SELECT 1
        FROM dbo.Products_Colours PC
        WHERE C.Colour = PC.Colour
          AND PC.fk_ProdID = P.pk_ProdID)
  );