# Joining three tables

**URL:** https://discourse.laminas.dev/t/joining-three-tables/354
**Category:** Components & MVC
**Tags:** zend-mvc, zend-db
**Created:** [November 5, 2017, 4:01pm UTC](https://discourse.laminas.dev/t/joining-three-tables/354 "2017-11-05T16:01:14Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![erfan](https://avatars.discourse-cdn.com/v4/letter/e/c4cdca/32.png) [@erfan](https://discourse.laminas.dev/u/erfan)
#### Post date: [November 5, 2017, 4:01pm UTC](https://discourse.laminas.dev/t/joining-three-tables/354/1 "2017-11-05T16:01:14Z")

</div>

hi there!  
I have a model by the name of ‘Keywords.php’ with the following content:  
namespace Blog\Model;

\<?php class Keywords { private $id; private $keyword; private $counter; public function \_\_construct ($id,$keyword,$counter){ $this-\>id = $id; $this-\>keyword = $keyword; $this-\>counter = $counter; } public function getId(){ return $this-\>id; } public function getKeyword(){ return $this-\>keyword; } public function getCounter(){ return $this-\>counter; } } and my repository by the name of 'KeywordsRepository.php' that contains the below code: \<?php namespace Blog\Model;/\*\* use InvalidArgumentException; use RuntimeException; use Zend\Db\ResultSet\ResultSet; // Replace the import of the Reflection hydrator with this: use Zend\Hydrator\HydratorInterface; use Zend\Db\Adapter\AdapterInterface; use Zend\Db\Adapter\Driver\ResultInterface; use Zend\Hydrator\Reflection as ReflectionHydrator; use Zend\Db\ResultSet\HydratingResultSet; use Zend\Db\Sql\Sql; class KeywordsRepository implements KeywordsRepositoryInterface { private $db; private $hydrator; private $keywordsPrototype; public function \_\_construct ( AdapterInterface $db, HydratorInterface $hydrator, Keywords $keywordsPrototype) { $this-\>db = $db; $this-\>hydrator =$hydrator; $this-\>keywordsPrototype = $keywordsPrototype; } public function findAllKeywords() { $sql = new Sql($this-\>db); $select = $sql-\>select('keywords') -\>order('counter DESC'); $statement = $sql-\>prepareStatementForSqlObject($select); $result = $statement-\>execute(); if ( !$result instanceof ResultInterface || !$result-\>isQueryResult()) { return[]; } $resultSet = new HydratingResultSet($this-\>hydrator, $this-\>keywordsPrototype); $resultSet-\>initialize($result); return $resultSet; } public function findKeyword($pid) { $sql = new Sql($this-\>db); $select = $sql-\>select('keywords') -\>join(['k'=\>'keywords\_relation'],'keywords.id = k.kid') -\>where(['k.pid'=\>$pid]); $statement = $sql-\>prepareStatementForSqlObject($select); $result = $statement-\>execute(); if(!$result instanceof ResultInterface || !$result-\>isQueryResult()){ return[]; } $resultSet = new HydratingResultSet($this-\>hydrator,$this-\>keywordsPrototype); $resultSet-\>initialize($result); return $resultSet; } } i have three ralated tables : keywords: id |keyword|type|order ------------------------ ------------------------ posts: id | title | description | text --------------------------- -------------------------- keywords\_related: id | kid | pid the pid is the identifier for posts.id and the kid is for keywords.id . the case is in KeywordsRepository-\>findKeyword() i have passed $pid as the id of the post and i want to act like this: public function findKeyword($pid) { $sql = new Sql($this-\>db); $select = $sql-\>select('keywords') -\>join(['k'=\>'keywords\_relation'],'keywords.id = k.kid') -\>where(['k.pid'=\>$pid]); $statement = $sql-\>prepareStatementForSqlObject($select); $result = $statement-\>execute(); if(!$result instanceof ResultInterface || !$result-\>isQueryResult()){ return[]; } $resultSet = new HydratingResultSet($this-\>hydrator,$this-\>keywordsPrototype); $resultSet-\>initialize($result); return $resultSet; } the $pid is obtained right in my controller here: $id = $this-\>params()-\>fromRoute('id'); the problem is when joinig these three tables: $select = $sql-\>select('keywords') -\>join(['k'=\>'keywords\_relation'],'keywords.id = k.kid') -\>join(['p'=\>'posts'],['k.kid'=\>'p.id']) -\>where(['p.id'=\>$pid]); how should i provide the right query to solve the problem?????? it would be kind of you to help me out!!!!

---

<div class="post-metadata">

### Author: ![erfan](https://avatars.discourse-cdn.com/v4/letter/e/c4cdca/32.png) [@erfan](https://discourse.laminas.dev/u/erfan)
#### Post date: [November 5, 2017, 4:54pm UTC](https://discourse.laminas.dev/t/joining-three-tables/354/2 "2017-11-05T16:54:49Z")

</div>

I have figured it out by chance:

```
$sql->select('keywords_relation')
->join(['k'=>'keywords'],'k.id = keywords_relation.kid')
->join(['p'=>'posts'],'p.id = keywords_relation.pid')
->where(['keywords_relation.pid'=>$pid]);

```

just select from the table to which we use joining tables.