tags:

views:

259

answers:

2

I have an SQL SELECT statement joining 2 tables.
The main table contains misc information on a product and is joined to a second table of sizes.
The second table contains a list of non-numeric sizes stored as a string and is simply structured as follows...

SizeID = Primary Key
SizeName = String value of size (i.e. Small, Medium, Large)
SizeOrder = Integer value to sort the order of sizes (i.e. A SizeOrder of 5 would mean the Size was larger than a SizeOrder of 2)

I need the SELECT statement to return the MIN() and MAX() sizes from the table of sizes.
However, as the actual size is stored as a string, i need to run the MIN() and MAX() functions against the SizeOrder column, but return the value of the SizeName column.

My current attempt is as follows:

SELECT ProductReference, MIN(SizeOrder) AS MinSizeID, MAX(SizeOrder) AS MaxSizeID, 
(SELECT SizeName FROM Size WHERE SizeOrder = MinSizeID) AS MinSizeText, 
(SELECT SizeName FROM Size WHERE SizeOrder = MaxSizeID) AS MaxSizeText
FROM (Product INNER JOIN Size ON Products.SizeFK = StoneSize.SizeID) 
WHERE ID = 132
GROUP BY ProductReference;

This returns the error "Reference 'MinSizeID' not supported (reference to group function)"

+2  A: 

MySQL is complaining about your using of the alias of the columns in the subselects, try

SELECT ProductReference, MIN(SizeOrder) AS MinSizeID, MAX(SizeOrder) AS MaxSizeID, 
(SELECT SizeName FROM Size WHERE SizeOrder = MIN(Products.SizeOrder)) AS MinSizeText, 
(SELECT SizeName FROM Size WHERE SizeOrder = MAX(Products.SizeOrder)) AS MaxSizeText
FROM (Products INNER JOIN Size ON Products.SizeFK = StoneSize.SizeID) 
WHERE ID = 132
GROUP BY ProductReference;

although I'm not sure if that will complain about selecting columns not in the group by.

Vinko Vrsalovic
I think as you may have been expecting, your suggestion returns an error stating "Invalid use of group function"
ticallian
That's not exactly what I was expecting. That may be because the MIN(SizeOrder) we are interested in is not the SizeOrder, I'd try qualifying the SizeOrder in the subselects, like this: WHERE SizeOrder = MIN(Products.SizeOrder) and the same for MAX().
Vinko Vrsalovic
That sadly generates the same "Invalid use of group function" error
ticallian
Oops, small typo in my test, that now works perfect - many thanks!
ticallian
A: 

You should do the whole query using the SizeOrder fields only from Size table, and then join the result with the Size table for the SizeName fields.

Zed