CODEWITHSUNDEEP
mysqlmagento
Lucas Scholten
Lucas Scholten

Reputation: 915

Group concat limit 1

Here is my group concat query

GROUP_CONCAT(DISTINCT IF(catalog_product_entity_varchar.attribute_id = '134',catalog_product_entity_varchar.value,NULL)) AS 'Subtitle'

The table contains multiple rows with 134 as the attribute id. Really all I want to do is pull the first catalog_product_entity_varchar.value WHERE catalog_product_entity_int.attribute_id=134

The full SQL is as follows

SELECT catalog_product_entity.sku AS SKU,
    IF(catalog_product_entity_decimal.attribute_id = '67',catalog_product_entity_decimal.value,NULL) AS 'Price',
    GROUP_CONCAT(DISTINCT IF(catalog_product_entity_int.attribute_id = '146',catalog_product_entity_int.value,NULL)) AS 'Brand',
    GROUP_CONCAT(DISTINCT IF(catalog_product_entity_varchar.attribute_id = '63',catalog_product_entity_varchar.value,NULL)) AS 'Name',
    GROUP_CONCAT(DISTINCT IF(catalog_product_entity_varchar.attribute_id = '134',catalog_product_entity_varchar.value,NULL)) AS 'Subtitle'
FROM catalog_product_entity
    JOIN catalog_product_entity_decimal ON catalog_product_entity_decimal.entity_id = catalog_product_entity.entity_id
    JOIN catalog_product_entity_int ON catalog_product_entity_int.entity_id = catalog_product_entity.entity_id
    JOIN catalog_product_entity_varchar ON catalog_product_entity_varchar.entity_id = catalog_product_entity.entity_id          
WHERE catalog_product_entity.type_id='simple' AND catalog_product_entity.sku='30020262'
GROUP by catalog_product_entity.entity_id

Edit: An attempt to make things clearer.

I'm pulling off data for products. To make things easier, try to ignore most of the query. We will concentrate only on the 'sku' and the 'subtitle'.

The first table catalog_product_entity. We are pulling off the SKU for each product. There is also a unique 'entity_id'.

We use the 'entity_id' when we want to pull off all associated details for this product from another table called catalog_product_entity_varchar.

The attribute id refers to which particular information you want. 134 is subtitle and 63 is the name. There are other 'attributes' as well that each have an id.

I've used an IF statement to allow me to assign 134 to 'subtitle' and 63 to 'name'. The table looks something like this.

entity_id   attribute_id   store_id   value
1                134         0        green, large
1                134         1        green, large
1                134         2        green, large
1                63          0        dakine day hiker bag
1                63          1        dakine day hiker bag
1                63          2        dakine day hiker bag

Notice how one product seems to have the same subtitle stored 3 times. This is because the ecommerce platform I'm using (Magento) allows you to store a different subtitle for each store you having running (one Magento install allows multiple domains to share the same product database, but have separate subtitles/descriptions,etc)

I have used Group_Concat to grab the data and then DISTINCT to make sure I only grab one subtitle. However the problem I'm facing is that most of the time my subtitles and names are exactly the same across all stores (so Distinct works) but when there are different subtitles distinct will grab more than one subtitle.

To be honest I'm not great with MYSQL and feel I might somehow omit group_concat altogether, maybe using nested SQL statements. Bear in mind we don't always know what store ids a product will use.

I don't really care which subtitle I grab, I just want one, the first one, the min one, the max one. Whatever.

Upvotes: 1

Views: 667

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 754280

You say:

The table contains multiple rows with 146 as the attribute id. Really all I want to do is pull the first catalog_product_entity_int.value WHERE catalog_product_entity_int.attribute_id=146.

What you say you want is implemented by:

SELECT Value
  FROM Catalog_Product_Entity_Int
 WHERE Attribute_ID = 146;

This might return multiple values, so you have to define what you mean by 'first'; it could be MIN, MAX, LIMIT 1, or some other criterion.

SELECT MIN(Value) AS Value
  FROM Catalog_Product_Entity_Int
 WHERE Attribute_ID = 146;

SELECT MAX(Value) AS Value
  FROM Catalog_Product_Entity_Int
 WHERE Attribute_ID = 146;

SELECT Value
  FROM Catalog_Product_Entity_Int
 WHERE Attribute_ID = 146
 LIMIT 1;

For what you say you want, I see no reason to join the other three tables or to use GROUP_CONCAT.

That leads me to suspect that you have not told us what you really want because the query shown is dramatic overkill for the stated requirement.

Upvotes: 1

Related Questions