Geek Answers Handbook

SQL join for accurate result

I have the following table in my database. His goal is to keep a set of colors. That is, [red + black], [blue + green + yellow], etc.

CREATE TABLE `df_productcolours` 
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_colourSet` int(11) NOT NULL,
  `id_colour` int(11) NOT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE` (`id_colourSet`,`id_colour`),
  KEY `idx_colourSet` (`id_colourSet`),
  KEY `idx_colour_id` (`id_colour`),
  CONSTRAINT `fk_colourid` FOREIGN KEY (`id_colour`) REFERENCES `df_lu_color` (`id`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION
)

I made a saved proc that takes an id_colour integers array as input and returns the color set identifier. What he wanted to do was return a set containing these colors, and ONLY those colors that are provided as input. What he actually does is return sets containing the desired colors, as well as some others.

This is the code that I still have:

SET @count = (SELECT COUNT(*) FROM tempTable_inputColours);

SELECT A.id_colourSet
FROM df_productcolours AS A
INNER JOIN tempTable_inputColours AS B
    ON A.id_colour = B.id_colour
GROUP BY A.id_colourSet
HAVING COUNT(A.id_colour) = @count
    AND COUNT(B.id_colour) = @count;

I have a feeling that the problem is with how I join, but I just can’t understand. Any help would be greatly appreciated. Thank.

+4
source
2

, . :

SET clrCount = (SELECT COUNT(*) FROM _tmp_ColourSet);

-- The first half of the query does an inner join, 
-- it will return all sets that have ANY of our requested colours. 
-- But the HAVING condition will make it return sets that have AT LEAST all of the colours we are requesting.
-- So at this point we have all the super-sets, if you will.
-- Then, the second half of the query will restrict that further, 
-- to only sets that have the same number of colours as we are requesting.
-- And voila :)

                      -- FIND ALL COLOUR SETS THAT HAVE ALL REQUESTED COLOURS
    SET colourSetId = (SELECT A.id_colourSet 
                        FROM df_productcolours AS A
                        INNER JOIN _tmp_colourset AS B
                            ON A.id_colour = B.id_colour
                        GROUP BY A.id_colourSet
                        HAVING COUNT(A.id_colour) = clrCount
                        -- FIND ALL COLOUR SETS THAT HAVE EXACTLY N COLOURS
                        AND A.id_colourSet IN (SELECT A.id_colourSet
                                                FROM df_productcolours AS A
                                                GROUP BY A.id_colourSet
                                                HAVING COUNT(A.id_colour) = clrCount));

, -, .

0

:

SELECT A.id_colourSet
FROM df_productcolours AS A
INNER JOIN tempTable_inputColours AS B
ON A.id_colour = B.id_colour
WHERE A.id_colourSet IN (SELECT id_colour FROM tempTable_inputColours)
AND A.id_colour IN (SELECT id_colour FROM tempTable_inputColours)


SELECT A.id_colourSet
FROM df_productcolours AS A
INNER JOIN tempTable_inputColours AS B
ON A.id_colour = B.id_colour
WHERE A.id_colourSet =(SELECT SUM(id_colour) FROM tempTable_inputColours)
0

More articles:


All Articles