[SOLVED] Searching through the GROUP_CONCAT values?

Issue

If I have 5 names returned in my group concat, is it somehow possible to further limit the returned table? If I for example only wish to see the firms with the name of "Assembly", and if I use that in an AND

"AND cell_name = 'Assembly'"

Then my group concat will only show Assembly, not the rest of the 5 names, even though I would like that.

SELECT
GROUP_CONCAT(DISTINCT cell_name ORDER BY cell_name ASC) as Cell,
integrator_country as Country,
integrator_name as Name

FROM sme_cells_integrator_link

INNER JOIN sme_cells ON
(sme_cells.id = sme_cells_integrator_link.fk_cells_id)

INNER JOIN sme_integrator ON
(sme_integrator.id = sme_cells_integrator_link.fk_integrator_id)

WHERE integrator_country = 'Denmark' 
GROUP BY integrator_name

What I would like is for it to return the type of order which have been selected, but still also show all the orders that firm has, just like GROUP_CONCAT normally does.

Without my AND, the returned data looks sort of like this:

+-----------------------------------------------------+---------+--------------+
| Assembly,Packaging                                  | Denmark | Firm 1       |
| Packaging,Palletizing                               | Denmark | Firm 2       |
| Handling                                            | Denmark | Firm 3       |
| Grinding / Finishing,Handling,Packaging,Palletizing | Denmark | Firm 4       |
+-----------------------------------------------------+---------+--------------+

WITH my AND, it looks like this:

+----------+-----------+--------+
| Assembly |  Denmark  | Firm 1 |
+----------+-----------+--------+

And what I would like is for it to look like this:

+--------------------+-----------+-------+
| Assembly,Packaging |  Denmark  | Firm 1|
+--------------------+-----------+-------+

Solution

We need your tables not the result, then we can make you a subselect.

But the other quick and dirty option is Having:

SELECT
GROUP_CONCAT(DISTINCT cell_name ORDER BY cell_name ASC) as Cell,
integrator_country as Country,
integrator_name as Name

FROM sme_cells_integrator_link

INNER JOIN sme_cells ON
(sme_cells.id = sme_cells_integrator_link.fk_cells_id)

INNER JOIN sme_integrator ON
(sme_integrator.id = sme_cells_integrator_link.fk_integrator_id)

WHERE integrator_country = 'Denmark' 
GROUP BY integrator_name
HAVING Cell LIKE '%Assembly%'

As you can see, you would have a Problem with similar Names with this solution.

Answered By – A. Blub

Answer Checked By – Mildred Charles (BugsFixing Admin)

Leave a Reply

Your email address will not be published.