Retrieve a list of values from a lookup table using db_select 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-&gt;fields('j');
$query-&gt;join('job_specialties', 'js', 'j.job_number = js.job_number');
$query-&gt;join('specialties', 's', 's.specialty_code = js.specialty_code');
$result = $query-&gt;distinct()-&gt;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.

&lt;table&gt;
  &lt;thead&gt;
    &lt;tr&gt;
      &lt;th&gt;Job Number&lt;/th&gt;
      &lt;th&gt;Title&lt;/th&gt;
      &lt;th&gt;Location&lt;/th&gt;
      &lt;th&gt;Specialty&lt;/th&gt;
    &lt;/tr&gt;
  &lt;/thead&gt;
  &lt;tr&gt;
    &lt;td&gt;12345&lt;/td&gt;
    &lt;td&gt;Some Cool Job&lt;/td&gt;
    &lt;td&gt;California&lt;/td&gt;
    &lt;td&gt;SQL&lt;br&gt;HTML5&lt;br&gt;JavaScript&lt;/td&gt;
  &lt;/tr&gt;
&lt;/table&gt;
Add more fields to your query from the other tables that you have joined to:

$query-&gt;fields('js');
$query-&gt;fields('s');


Use hook_menu() to create your page if you want a custom one, then render it in PHP (I'm guessing at your result variable):

&lt;table&gt;
  &lt;thead&gt;
    &lt;tr&gt;
       ....
    &lt;/tr&gt;
    &lt;tr&gt;
      &lt;th&gt;&lt;?php&gt;$result['job_number']&lt;?&gt;&lt;/th&gt;
      .....
 &lt;/table&gt;