I'm running MySQL 5.0.88 (Coldfusion8)
I have a product search which I'm querying number-of-results
as well as min/max prices/rebates
across the product table. I also want to include a string of distinct sizes/colors
, so I can update my search criteria along with displaying the results.
However my GROUP_CONCAT does not return all expected values. I don't know what I'm missing, but it seems it is only returning Distinct min/max
sizes/colors versus returning all
Distinct sizes/colors across the recordset.
My product table:
CREATE TABLE dummy (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`iln` VARCHAR(13) NULL DEFAULT NULL,
`ean` VARCHAR(35) NULL DEFAULT NULL,
`artikelnummer` VARCHAR(35) NULL DEFAULT NULL,
`groesse` VARCHAR(10) NULL DEFAULT NULL,
`farbe` VARCHAR(35) NULL DEFAULT NULL,
`farbnummer` VARCHAR(10) NULL DEFAULT NULL,
`preis_ek` DECIMAL(12,2) NULL DEFAULT NULL,
`preis_vk` DECIMAL(12,2) NULL DEFAULT NULL,
`preis_aktuell` DECIMAL(12,2) NULL DEFAULT NULL,
`firma` VARCHAR(35) NULL DEFAULT NULL,
`marke` VARCHAR(35) NULL DEFAULT NULL,
`nos` VARCHAR(4) NULL DEFAULT NULL,
`nos_anzeige` VARCHAR(4) NULL DEFAULT NULL,
`aktiv` VARCHAR(4) NULL DEFAULT NULL,
`modus` VARCHAR(4) NULL DEFAULT NULL,
`bestand` DECIMAL(10,0) NULL DEFAULT '0'
)
Products are listed by EAN, so for example a Shirt in Size S,M,L,XL would have 4 entries like so:
style ean size price qty
123 111111111111 S 9.99 12
123 111111111112 M 9.99 1
123 111111111113 L 9.99 23
123 111111111114 XL 9.99 0
Here is my Query:
SELECT COUNT(recordcount) AS total_records
, MIN(min_price_ek) AS ek_min
, MAX(max_price_ek) AS ek_max
, MIN(min_price_vk) AS vk_min
, MAX(max_price_vk) AS vk_max
, MAX(max_reb) AS rb_max
, SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT sizeRange ), ',', 10 ) AS sz_rng
, SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT colorRange ), ',', 16 ) AS cl_rng
FROM (SELECT a.id AS recordcount
, a.nos
, a.nos_anzeige
, MAX(IFNULL(p.ek, a.preis_ek)) AS max_price_ek
, MIN(IFNULL(p.ek, a.preis_ek)) AS min_price_ek
, MAX(IFNULL(p.vk, a.preis_vk)) AS max_price_vk
, MIN(IFNULL(p.vk, a.preis_vk)) AS min_price_vk
, ROUND( MAX( ( IFNULL(p.ek, a.preis_ek) - IFNULL(p.vk ,a.preis_aktuell) ) / IFNULL(p.ek, a.preis_ek) ),2) AS max_reb
, a.groesse AS sizeRange
, zu.systemfarbe AS colorRange
FROM artikelstammdaten a
# currency join
LEFT JOIN preislisten p ON
p.iln = a.iln
AND p.ean = a.ean
AND ( ( p.preisliste = "Test" AND p.iln = "2222222222222" ) OR (1=0) )
# base color join
LEFT JOIN farbenzuordnung zu ON
a.farbe = zu.farbe
WHERE a.aktiv = "ja"
# include currency if applicable
AND ( IF( a.iln IN ( 2222222222222), p.onlinepreis IS NOT NULL,1 ) )
AND a.artikelnummer LIKE "%style_number%"
GROUP BY a.iln, a.artikelnummer
HAVING (( sum(a.bestand) != 0 ) OR (a.nos = "ja" AND a.nos_anzeige = "ja" ))
) AS temp
I need to group by seller (a.iln
) and style (a.artikelnummer
). The having
clause only selects products that have inventory (sum(a.bestand) != 0
) or are available never-out-of-stock (a.nos
set on product and a.nos_anzeige
feature active).
My problem is selecting distinct sizes/colors done in these lines:
, SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT sizeRange ), ',', 10 ) AS sz_rng
, SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT colorRange ), ',', 10 ) AS cl_rng
....
, a.groesse AS sizeRange
, zu.systemfarbe AS colorRange
This does not work properly. The above article would return only s,xl
vs s,m,l,xl
and I'm not really sure why. I'm using the SUBSTRING_INDEX
to only select the first 10 sizes/colors (TOP 10 would be nicer), but I'm not sure this is the problem.
Question:
Is there something wrong in the syntax which causes not all distinct sizes/colors to be returned? Is there a way to only retur