MySQL Distinct Group By problem
I've got a table that has:
a unique ID (proId)
a product ID (proawningId)
a "upto" width column (prowidth)
an "arm length" column (proarms)
The table is for patio awnings and it stores what projection arm length you'll need for various widths of awnings. For instance if you want an awning 2.75 metres width you look at the data find your width in the width column (although this column shows the maxium width for each awning and arm combination) so an entry might be upto width 2.98 = arm 2.5m for awning type 1.
What I'm trying to do is to create a quick look-up that populates a select drop down list. A user enters their width and clicks submit the system then queries the database and populates the select list with the possible "arm" lengths they can have.
This is the SQL statement I'm currently using:
SELECT proarms, proId, proawningId, prowidth FROM ukawnings.tblawningprojections WHERE proawningId = awningId AND prowidth >= (this is the width entered on the form) GROUP BY proawningId ORDER BY proarms ASC
This almost works but I get a separate entry for each arm length for each awning type, e.g.
if I enter 2.75m as my width the select list looks like:
2.5M (awning type 1)
2.5M (awning type 2)
2.5M (awning type 3)
3.0M (awning type 4)
3.5M (awning type 5)
What I'd like to happen is there to be only one entry for each Arm length (so only one 2.5M in the example above). I tried adding DISTINCT(proarms) to the SQL but it doesn't make any difference.
Can anyone help.