I have a “jobs” table where each job can have multiple specialties. So I have a specialties table with a list of all available specialties and I have a lookup table that just has a job_number and a specialty_code in it so each job can be attached to multiple specialties. What I am trying to figure out is when I do a db_select, how can I have the job record print out once, but have a list of it’s specialties print out also as part of that record? Here is what I have right now for my query:
$query = db_select("jobs", "j"); $query->fields('j'); $query->join('job_specialties', 'js', 'j.job_number = js.job_number'); $query->join('specialties', 's', 's.specialty_code = js.specialty_code'); $result = $query->distinct()->execute(); This gives me the correct records, but I want to display the list of specialties each job is attached to and that’s where I’m not sure how to do that. Is there even a way using the database API to do that?
Essentially I would want to be able to display the records something like this. This is only one record, but you get the idea.
<table> <thead> <tr> <th>Job Number</th> <th>Title</th> <th>Location</th> <th>Specialty</th> </tr> </thead> <tr> <td>12345</td> <td>Some Cool Job</td> <td>California</td> <td>SQL<br>HTML5<br>JavaScript</td> </tr> </table> 
