php - A hard SQL Query -

i have unusual sql question i'm not quite sure how best explain please bear me. have 3 tables, 1 of volunteer organisations , 1 users , other of user details.

#table 1# ## name president secretary treasurer ---------- abc 571 123 456 def 457 582 444  #table 2# ## id name ---------- 571 joe blogs 123 joanne blogs  #table 3# ## id address city ---------- 571 123...... somewhere 123 456..... somewhere else 

what trying allow user assemble pdf address labels in order of organisation name, might want presidents every organisation or presidents , secretaries read presidents , secretary ids organisations , join them address details , assemble them in order of organisation. may choose presidents or presidents , secretaries or combination of above. must sort in organisation order , return user objects in order.

for example

abc -> president abc -> secretary def -> president def -> secretary 

i have tried few options. first to,

$query  = $db->getquery(true);         $query->select($db->quotename('a.name'));         $query->select($db->quotename($this->show_exec));         $query->from($db->quotename('#__agshows_shows', 'a'));         $tableacount = 'b';         $tablebcount = 'aa';         foreach($this->show_exec $column){             $query->select($db->quotename(array($tableacount . '.user_id', $tableacount . '.address_1', $tableacount . '.address_2', $tableacount . '.city', $tableacount . '.region', $tableacount . '.postal_code', $tablebcount . '.name')))             ->join('left', $db->quotename('#__agshows_user_profile', $tableacount) . ' on (' . $db->quotename($tableacount . '.user_id') . ' = ' . $db->quotename($column) . ')')             ->join('left', $db->quotename('#__users', $tablebcount) . ' on (' . $db->quotename($tablebcount . '.id') . ' = ' . $db->quotename($column) . ')');              $tableacount ++;             $tablebcount ++;         }          switch($this->show_sort) {              case 'a.title':                 $query->order($db->quotename('a.title') . ' asc');             break;              case 'a.groupid':                 $query->order($db->quotename('a.groupid') . ' asc');             break;              default:                 $query->order($db->quotename('a.title') . ' asc');                       }          $db->setquery($query);         $show_users = $db->loadobjectlist(); 

the problem goes through foreach loop overwrite each other each organisation. want flexible ($this->show_exec holds array of columns choose, i.e a.president, a.secretary).

the second option was,

if(!empty($this->show_exec)) {         // show exec user ids.         $query  = $db->getquery(true);         $query->select($db->quotename($this->show_exec));         $query->from($db->quotename('#__agshows_shows'));         $db->setquery($query);         $show_users = $db->loadassoclist();           foreach ($show_users $shows) {             foreach ($shows $key=>$value) {                 if($value != 0) {                     $user_ids[]=$value;                  }             }         }     }  $query = $db->getquery(true);     $query->select('a.*');     $query->from($db->quotename('#__agshows_user_profile') . ' a');      // join on users names.     $query->select('uc.name')     ->join('left', '#__users uc on uc.id=a.user_id');      $query->where('a.user_id in ('. implode(',', $user_ids) . ')');      $query->order($db->escape('a.city asc'));      $db->setquery($query);     $recipients = $db->loadobjectlist(); 

the problem 1 $users_id in correct order when queries second table has no reference sort , returns in order of second table not order want....

is there clean way in one?

or there way can sort second query return results in order of $user_ids array?

i hope makes sense..

thanks

i think below query might work :

    select    *    (select      o.name org_name,     u1.name username,     'president' `type`       org o      left join users u1        on u1.`id` = o.`president`    union      select      o.name org_name,     u1.name username,     'secretary' `type`       org o      left join users u1        on u1.`id` = o.`secretary`    union      select      o.name org_name,     u1.name username,     'treasurer' `type`       org o      left join users u1        on u1.`id` = o.`treasurer`)  order a.org_name ; 

i have assumed primary key of users table used foreign key in president, secretary & treasurer columns in organization table.

below query's output produce 1 virtual column "type" can use in code determine role of particular user in organization. need create query dynamically take care of situation when president or secretary or treasurer or combination of them required.