# Fetch data inner join from two tables

**URL:** <https://discourse.cakephp.org/t/fetch-data-inner-join-from-two-tables/6493>\
**Category:** Need Help\
**Created:** [August 16, 2019, 10:33am UTC](https://discourse.cakephp.org/t/fetch-data-inner-join-from-two-tables/6493 "2019-08-16T10:33:40Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![arizzoye](https://avatars.discourse-cdn.com/v4/letter/a/f05b48/32.png) [@arizzoye](https://discourse.cakephp.org/u/arizzoye)\
**Post date:** [August 16, 2019, 10:33am UTC](https://discourse.cakephp.org/t/fetch-data-inner-join-from-two-tables/6493/1 "2019-08-16T10:33:40Z")

</div>

morning, I’m newbie in CakePHP 3.x, I want to fetch data with inner join two tables

> RulesSigns table

and

> ForwardsSigns table

below content tables in MySQL

```auto
RulesSigns
-----------------------------------------------------
ID | rule_id | sign_id | forwards_signs_id |
----|-------------|-----------|---------------------|
 12 | 4 | 28 | 5 |
 15 | 5 | 2 | 1 |
20 | 5 | 3 | 1 |
21 | 5 | 5 | 1 |
-----------------------------------------------------

ForwardsSigns
----------------------------------
ID | forward_id | sign_id |
----|----------------|-----------|
 1 | 1 | 2 |
 2 | 1 | 3 |
 3 | 1 | 5 |
----------------------------------

RulesSigns Table 
<?php
namespace App\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

class RulesSignsTable extends Table
{

    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('rules_signs');
        $this->setDisplayField('ID');
        $this->setPrimaryKey('ID');

        //compare field Sign
        $this->belongsTo('Signs', [
		'foreignKey' => 'sign_id',
		'joinType' => 'INNER'
	]);
   }
}
?>

RulesSigns Controller
<?php
namespace App\Controller;
use App\Controller\AppController;
class RulesSignsController extends AppController
{
   public function getsign()
   {
     $ruleSignsEntity = $this->RulesSigns->find('all')
  	 ->where(['forwads_sign_id' => $this->request->data['sign_id']])
	 ->contain(['ForwardsSigns'])
	->first();
	//now content RulesSign table fill Sign table
     $signGejala = $ruleSignsEntity->ForwardsSigns->sign_id;
     $this->set('signGejala', $signGejala);
  }
}

getsign.ctp
<?php if (!empty($signGejala->sign_id)): ?>
<table>
  <tr><td><h4><strong>query RulesSign ke Gejala</strong></h4></td></tr>
  <tr>
      <th><?= __('Gejala') ?></th>
  </tr>
  <?php foreach ($signGejala as $sign): ?>
  <tr>
       <td><?= h($sign->sign_id) ?></td>
   </tr>
   <?php endforeach; ?>
 </table>
 <?php endif; ?>

```

the output inner join that I want is below

```auto
RulesSign table ForwardsSign table
----------------------------------------------------------------------------------
ID | rule_id | sign_id | forwards_signs_id | ID | sign_id |
----|-------------|-----------|---------------------|------------|----------------
    | | | | | |
 15 | 5 | 2 | 1 | 1 | 2 |
20 | 5 | 3 | 1 | 2 | 3 |
21 | 5 | 5 | 1 | 3 | 5 |
---------------------------------------------------------------------------------

```

the error messages shown here

```auto
Notice (8): Undefined index: sign_id [APP/Controller\RulesSignsController.php, line 82]

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'forwads_sign_id' in 'where clause' 

 SQL Query:

SELECT RulesSigns.ID AS `RulesSigns __ID`, RulesSigns.rule_id AS `RulesSigns__ rule_id`, RulesSigns.sign_id AS `RulesSigns __sign_id`, RulesSigns.forwards_sign_id AS `RulesSigns__ forwards_sign_id`, ForwardsSigns.ID AS `ForwardsSigns __ID`, ForwardsSigns.forward_id AS `ForwardsSigns__ forward_id`, ForwardsSigns.sign_id AS `ForwardsSigns__sign_id` FROM rules_signs RulesSigns LEFT JOIN forwards_signs ForwardsSigns ON ForwardsSigns.ID = (RulesSigns.forwards_sign_id) WHERE forwads_sign_id = :c0 LIMIT 1

```

I hope someone help me to fix join inner two tables,  
thanks

---

<div class="post-metadata">

**Author:** ![mokiwaa](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@mokiwaa](https://discourse.cakephp.org/u/mokiwaa)\
**Post date:** [August 16, 2019, 11:44am UTC](https://discourse.cakephp.org/t/fetch-data-inner-join-from-two-tables/6493/2 "2019-08-16T11:44:19Z")

</div>

try

```auto
$ruleSignsEntity = $this->RulesSigns->find('all')
  	 ->where(['forwards_sign_id' => $this->request->data['sign_id']])
	 ->contain(['ForwardsSigns'])
	->first();

```

because you write wrong the column forwards\_sign\_id name in the query I think… 😉

---

<div class="post-metadata">

**Author:** ![arizzoye](https://avatars.discourse-cdn.com/v4/letter/a/f05b48/32.png) [@arizzoye](https://discourse.cakephp.org/u/arizzoye)\
**Post date:** [August 16, 2019, 11:46am UTC](https://discourse.cakephp.org/t/fetch-data-inner-join-from-two-tables/6493/3 "2019-08-16T11:46:43Z")

</div>

hmm…houw should the true, I was try one by one columns…🧐