mysql group by or concat

[jarvis]

Hi,

I really hope someone can help.

 

I've several tables containing various info. I therefore have to join one table to another.

SELECT 
$table_products.productID,
$table_products.code,
$table_products.name,
$table_products.shortdescription,
$table_products.description,
$table_products.thumbnail,
$table_products.mainimage,
$table_products.extrafield1,
$table_products.extrafield2,
$table_products.extrafield3,
$table_products.price1,
$table_products.weight,
$table_products.scLevel,
$table_extrafields_values.content
FROM $table_products LEFT JOIN $table_extrafields_values
ON $table_products.productID = $table_extrafields_values.productID
AND $table_extrafields_values.extraFieldID=4
WHERE (
   $table_products.productID 
   BETWEEN 2 And 999999) 
AND 
($table_products.visible = 'Y' or $table_products.allowDirect = 'Y') 
GROUP BY 
$table_products.productID
ORDER BY 
$table_products.productID

 

The above works well, however, I had to add GROUP BY $table_products.productID to prevent multiple items. Although this works great, it's now causing an issue with the results. Before I added GROUP BY, each product would return with multiple values from this column $table_extrafields_values.extraFieldID=4

 

What I need to do is show one item but with multiple results from here. To help explain, the item results are shows and $table_extrafields_values.extraFieldID=4 is the shoe sizes.

 

So it was showing

PROD ID = 1

SHOE NAME = DM

SIZE = 7

 

PROD ID = 1

SHOE NAME = DM

SIZE = 8

 

PROD ID = 1

SHOE NAME = DM

SIZE = 9

 

What I need to do is

 

PROD ID = 1

SHOE NAME = DM

SIZE = 7,8,9

 

I know it can be done but I can't remember how, I think it's Concat or Group By

 

Can someone help?

 

Thanks

 

 

 

[jarvis]

Ah I think I've sussed it:

SELECT 
$table_products.productID,
$table_products.code,
$table_products.name,
$table_products.shortdescription,
$table_products.description,
$table_products.thumbnail,
$table_products.mainimage,
$table_products.extrafield1,
$table_products.extrafield2,
$table_products.extrafield3,
$table_products.price1,
$table_products.weight,
$table_products.scLevel,
group_concat($table_extrafields_values.content separator ', ') 
FROM $table_products LEFT JOIN $table_extrafields_values
ON $table_products.productID = $table_extrafields_values.productID
AND $table_extrafields_values.extraFieldID=4
WHERE (
   $table_products.productID 
   BETWEEN 2 And 999999) 
AND 
($table_products.visible = 'Y' or $table_products.allowDirect = 'Y') 
GROUP BY 
$table_products.productID
ORDER BY 
$table_products.productID

I think that does the job I need!

[fenway]

You can't use GROUP BY like that.

[jarvis]

Thanks Fenway, if you don't mind me asking, why not? The query still seems to run ok but would rather know I've used the correct query

 

Sorry, this isn't my area so muddling through

 

Thanks

[fenway]

Why do you have duplicates?  GROUP BY isn't the same as DISTINCT.