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)