CODEWITHSUNDEEP
mysqlsqldatabase
talkpoppycock
talkpoppycock

Reputation: 147

mySQL GROUP_CONCAT - Query

I can get nearly what I require, from a sql query but not quite and I would appreciate some help.

I have the following three tables:

people
+----+-------------------------+
| id | email                   | 
+----+-------------------------+
|  1 | [email protected]    |
|  2 | [email protected]    |
|  3 | [email protected] |
+----+-------------------------+

jobs
+----+-------------+
| id | description | 
+----+-------------+
|  1 | Plumber     | 
|  2 | Plasterer   | 
|  3 | Carpenter   | 
|  4 | Builder     | 
+----+-------------+

people_jobs
+-----------+--------+
| person_id | job_id | 
+-----------+--------+
|     1     |   1    | 
|     1     |   3    | 
|     2     |   3    | 
|     3     |   3    | 
|     3     |   1    |
|     3     |   4    |
+-----------+--------+

Using this query I can output all the data in the format that I require:

SELECT people.id, people.email,
GROUP_CONCAT(DISTINCT jobs.description 
ORDER By jobs.description DESC SEPARATOR ', ')
FROM jobs
INNER JOIN people_jobs On people_jobs.job_id = jobs.id 
INNER JOIN people On people.id = people_jobs.person_id
GROUP BY people.id

As follows:

+----+-------------------------+----------------------------+
| id |         email           | GROUP_CONCAT               |
+----+-------------------------+----------------------------+
|  1 | [email protected]    | Plumber, Carpenter         |
|  2 | [email protected]    | Carpenter                  |
|  3 | [email protected] | Plumber, Carpenter,Builder |
+----+-------------------------+----------------------------+

By adding a WHERE clause I can optput all the people that can do a specific job:

SELECT people.id, people.email,
GROUP_CONCAT(DISTINCT jobs.description 
ORDER By jobs.description DESC SEPARATOR ', ')
FROM jobs
INNER JOIN people_jobs On people_jobs.job_id = jobs.id 
INNER JOIN people On people.id = people_jobs.person_id
WHERE jobs.description = 'Plumber'
GROUP BY people.id

+----+-------------------------+--------------+
| id |         email           | GROUP_CONCAT |
+----+-------------------------+--------------+
|  1 | [email protected]    | Plumber      |
|  3 | [email protected] | Plumber      |
+----+-------------------------+--------------+

What I want to achieve is the above output plus the other jobs that the people can do as follows:

+----+-------------------------+----------------------------+
| id |         email           | GROUP_CONCAT               |
+----+-------------------------+----------------------------+
|  1 | [email protected]    | Plumber, Carpenter         |
|  3 | [email protected] | Plumber, Carpenter,Builder |
+----+-------------------------+----------------------------+

Can anyone help please?

Upvotes: 3

Views: 38

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Use HAVING clause instead:

SELECT people.id, people.email,
       GROUP_CONCAT(DISTINCT jobs.description 
       ORDER By jobs.description DESC SEPARATOR ', ')
FROM jobs
INNER JOIN people_jobs On people_jobs.job_id = jobs.id 
INNER JOIN people On people.id = people_jobs.person_id
GROUP BY people.id
HAVING COUNT(CASE WHEN jobs.description = 'Plumber' THEN 1 END) >= 1

HAVING filters out people groups not having at least one job description equal to 'Plumber', whereas WHERE operates per row, excluding all rows not having a job description equal to 'Plumber'.

Demo here

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

In MySQL, you can add a simple having clause, similar to the where clause:

SELECT p.id, p.email,
       GROUP_CONCAT(DISTINCT j.description ORDER By j.description DESC SEPARATOR ', ')
FROM jobs j INNER JOIN
     people_jobs pj
     On pj.job_id = j.id INNER JOIN
     people p
     On p.id = pj.person_id
GROUP BY p.id
HAVING SUM(j.description = 'Plumber') > 0;

Also, you probably do not need the DISTINCT in the GROUP_CONCAT(), unless your tables have duplicates.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175934

You can wrap your first query with subquery and use FIND_IN_SET:

SELECT *
FROM (
   SELECT people.id, people.email,
     GROUP_CONCAT(DISTINCT jobs.description 
     ORDER By jobs.description DESC SEPARATOR ', ') AS jobs
   FROM jobs
   JOIN people_jobs On people_jobs.job_id = jobs.id 
   JOIN people On people.id = people_jobs.person_id
   GROUP BY people.id) AS sub
WHERE FIND_IN_SET('Plumber', sub.jobs) > 0;

SqlFiddleDemo

But it will be slower than HAVING approach in Giorgos Betsos answer.

Upvotes: 1

Related Questions