Two queries in a dynamically generated table? a better way?

[beanfair]

We are working on a website that has a directory of teachers. The specs calls for a page that shows all of the teachers in a specific region.  That is the easy part.  The database has a main table of practitioners and another table of regions with the ID of the practitioner as the foreign key so practitioners may have multiple regions in which they teach.  On a region page, we want the practitioner's picture, name and ALL regions.  Getting all of the regions to show is the problem.  We are thinking we would need to run another query within the table cell but really want to avoid that.

 

--------------------------------------------------------------------------------

SELECT sacred_ads.sacred_adsID, sacred_ads.adURL, sacred_ads.adName,

sacred_ads.practType, sacred_adLabel.adLabel, sacred_ads.thumbnail,

sacred_ads.adStatus, sacred_adLocations.adCity,

sacred_adLocations.adState, stateRegions.region FROM sacred_ads INNER

JOIN (sacred_adLabel INNER JOIN (sacred_adLocations INNER JOIN

stateRegions ON stateRegions.stateRegionsID =

sacred_adLocations.adState) ON sacred_adLabel.adLabelID =

sacred_ads.adLabel) ON sacred_ads.sacred_adsID =

sacred_adLocations.sacred_adsID WHERE sacred_ads.adStatus =2 AND

stateRegions.region = [[VAL]] AND sacred_ads.practType = [[VAL]];

--------------------------------------------------------------------------------

we use php to dynamically create and populate the table.

Basically we need to the the contents of this query

--------------------------------------------------------------------------------

SELECT adCity, adState, FROM sacred_adLocations WHERE sacred_adsID = [[VAL]]

--------------------------------------------------------------------------------

to the table cell.

 

Any suggestions are most appreciated, 

[fenway]

First, why all the parens? Second, that's impossible to read... and third, what's the question?

[beanfair]

First, the paren are to make it easier to read :-)

 

In a nutshell there is a directory table of teachers, one record per teacher.  A second table holds the teacher ID as the foreign key in a one to many relationship with one row per teaching location.  Some teachers have multiple teaching locations.

 

The large multi-table query creates a recordset of teachers by teacher gender,  status and location.  That record set populates a table that forms the teacher directory.  The teacher directory displays thumbnail, name and one teaching location. That is easy and works great.

 

The problem is that they want ALL of the teaching locations that pertain to a teacher to show in this directory.  How to create a table based on a query that returns one record per teacher THEN with in the cell, print the results of ANOTHER recordset that might have 1-4 records per teacher ID

 

 

[fenway]

GROUP_CONCAT()?