How to create dynamic query for inner join on same table? [closed]

Question

I want a dynamic query to do inner join on same table.

My query is:

SELECT t1.table1 FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id AND t1.name = t2.name"

I want to join table1 and table2 based on id and name present in table2.

What is the equivalent dynamic query in Drupal for this? How this can be achieved?

Answer 1

$query = db_select('table1','t1');
$query->join('table2','t2','t1.id1=t2.id2');
$query->fields('t1', array('some fields'))
      ->fields('t2', array('some fields'));
$result = $query->execute()->fetchAll();

Then you can do print_r stuff into $result like

echo "<pre>";print_r($result);echo "</pre>";

If you have devel module installed and enabled, then you can also use:-

dsm($result);

This will give the whole result in form of array. If you need in form of object, then you need to modify few lines of code like this:-

$data = $result->execute();
while($result = $data->fetchObject()){
  //Do your related stuff
}

I think it will help a lot. Thanks.