I have three tables named: employees, products, and bid.
employees table structure below
id | employee_id | isid | ename| email | password |admin |practice| phone
bid table structure
bid_id | product_id | employee_id | bid_amount | LastUpdate
product table structure
product_id |employee_id |name|type|brand|model |condition|about|verified|reserved_price
Now I want write a query to get all values from "products" and "bid_id" "bid_amount" from "bid" table. Then, Employee name(ename) from "employees" table. How to write SQL query for that in Zend framework structure? Previously I've written a query to take all "products" table values and corresponding "bid_id" and "bid_amount" values from the bid table. Now I want to display the corresponding employee name(ename). So I need to join to "employees" table. can anyone help me to do this? My previous query for all "products" table values and "bid_id" "bid_amount" from bid table is below. It works perfectly.
public function fetchAllProductItems() {
$oSelect = $this->select()
->setIntegrityCheck(false)
->from(array("p" => "products","b" => "bid"), ('*'))
->joinLeft(array("b" => "bid"), "b.product_id=p.product_id", array('bid_id','bid_amount'))
->group('p.product_id')
->having("p.verified = ?", "Yes");
return $oSelect;
}
return $this->select()
->setIntegrityCheck(false)
->from(['p' => 'products'],['*'])
->joinLeft(['b' => 'bid'],'b.product_id=p.product_id', ['bid_id','bid_amount'])
->joinInner(['e' => 'employees'], 'e.employee_id=b.employee_id',['ename'])
->where("p.verified = 'Yes'")
->group('p.product_id');
or
return $this->select()
->setIntegrityCheck(false)
->from(['b' => 'bid'],['bid_id','bid_amount'])
->joinInner(['p' => 'products'],'p.product_id=b.product_id',[
'product_id',
'product_name' => 'name',
'product_type' => 'type',
'product_brand' => 'brand',
'product_model' => 'model',
//etc
])
->joinInner(['e' => 'employees'],'e.employee_id=b.employee_id',[
'employee_id',
'employee_name' => 'ename'
])
->where("p.verified = 'Yes'");