sqlmysqlselectunion-join

MySQL: How to write the select statement in 1 query?


I want to get the following client's record when receiving 2 info: province_id = 2002 and staff_id = 9

client_id client_name province_id
336 ebay 2002

Is it possible to make 1 select statement to get the result above?

My current approach is VERY messy....

I am currently using a select statement to get a list of exclusive client_id

select staff_id, client_id from exclusive where exclusive.staff_id = 9;

then once I get the (exclusive list),

$result = array();
foreach(exclusiveList as aList) {
  client_id = aList['client_id'];
  $aClient = client->getClientDetail($client_id); // an array with client's info
  if(aClient['province_id'] == 2002) {
      array_push($result, aClient);
}

This is the table I have:

Division: Assigning a staff to handle client in some provinces

staff_id province_id
9 2001

Exclusive: Assigning a specific client to the Staff outside of their province

staff_id client_id
9 437
9 336

Client: All Clients

client_id client_name province_id
1 Amazon 2001
336 ebay 2002
437 MarketPlace 2047

Province

province_id province_name
2001 Alberta
2002 Ottawa
2047 Manitoba

Thank you so much for your help, I have been working on this table for over a week before asking, as i have absolutely no clue on how to make it into one select statement.


Solution

  • The question is bit unclear but it sounds like you only need a simple join:

    select c.client_id, c.client_name, c.province_id
    from division d
      join exclusive e on e.staff_id=d.staff_id
      join client c on e.client_id=c.client_id
    where e.staff_id=9 and d.province_id=2002