Concat multiple rows in subselect? 1

[csteinhilber]

Alright, I'm pinned in one of those situations where the legacy database is not designed as I would've done it, yet I still have to get the desired data out.

I have a "jobs" table, "locations" table, and "departments" table. There is a 1:1 of jobs to locations (job can only be in one location), but there is a many-to-one of departments to jobs (a job can be active in many different departments).

Unfortunately, the "many-to-one" is accomplished by stuffing a field with a comma-separated list of department ids.

[tt]
jobs-
jobid jobname location depts
123 JOB X 1 2,3,4
124 JOB Y 2 3,4,5

locations-
locid locationname
1 SAN FRANCISCO
2 SAN DIEGO

departments-
deptid deptname
2 DEVELOPMENT
3 TESTING
4 MEDIA
5 ADVERTISING
[/tt]

... and I need a resultset that looks like:

[tt]
jobname locationname deptnames
JOBX SAN FRANCISCO DEVELOPMENT,TESTING,MEDIA
JOBY SAN DIEGO TESTING,MEDIA,ADVERTISING
[/tt]

I was hoping I could do something like

SELECT *,GROUP_CONCAT(SELECT dept.departmentname FROM departments dept WHERE dept.did IN jobs.depts) as deptnames FROM jobs,locations loc WHERE loc.locid=jobs.location ORDER BY jobs.jobname

but no joy.

The MySQL GROUP_BY documentation is a bit lacking in that it doesn't describe the table schemas used in the examples (actually, a huge flaw in a LOT of the MySQL documentation).

Anybody have any pointers or ideas?

Thanks in advance!
-Carl

 

[DonQuichote]

I think you need the FIND_IN_SET function. Not tested, but it should be something like:

SELECT *,GROUP_CONCAT(SELECT dept.departmentname FROM departments dept WHERE FIND_IN_SET(dept.did, jobs.depts)>0) as deptnames FROM jobs,locations loc WHERE loc.locid=jobs.location ORDER BY jobs.jobname

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

SELECT j.jobname
     , l.locationname
     , GROUP_CONCAT(d.deptname) AS deptnames
  FROM jobs AS j
INNER
  JOIN locations AS l
    ON l.locid = j.location
INNER
  JOIN departments AS d
    ON FIND_IN_SET(d.deptid,j.depts) > 0
GROUP
    BY j.jobname
     , l.locationname

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[csteinhilber]

Most excellent, Rudy!
Thank you so much!

So simple, even I can understand it ;-)



-Carl