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>
