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.
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