How to get 3 fields from 3 different tables in the database

Question

I need to pull LATITUDE from the latitude table, LONGITUDE from the longitude table. I also want to pull the TITLE from the standard node_field_data table where the NID matches the entity_id from either the longitude or latitude table. I should also mention that this is Drupal 8, so it's not easy to find this info on any forums yet. I didn't use the usual db_query, because it's deprecated.

The below is what I have so far, but I cannot figure out how to get the LATITUDE and LONGITUDE based on the NID of the Title.

I have to get three fields from three different tables in the database. TABLENAME FIELDNAME node__field_latitude field_latitude_value node__field_longitude field_longitude_value node_field_data title

This is the query I have now

  $db = \Drupal::database();
  $query1= $db
    ->select('node__field_latitude','t')
    ->fields('t',array('field_latitude_value'));
  $query2= $db
    ->select('node__field_longitude','b')
    ->fields('b',array('field_longitude_value'));
  $titles= $db
    ->select('node_field_data','c')
    ->fields('c',array('title'));    

  $query1->union($query2, 'UNION ALL');  
  $query1->join('node_field_data','alias','alias.nid = t.entity_id');



  $query1->union($titles, 'UNION ALL');

It works, but it gets all titles in the node_field_data table. There are only three that I need and the nid matches the entity_id in either the latitude table or the longitude table.

How can I fix this query to pull only the titles associate with the latitudes and longitudes I am pulling?

BONUS,

For some reason when I try to var_dump the results to an array, the array is creating two empty values. See my loop and the corresponding var_dump

  $result=$query1->execute();
  $test = array();
  if ($result) {
    while ($row = $result->fetchAssoc()) {
      $test[] = $row['field_latitude_value'];
      $test[] = $row['field_longitude_value'];
      $test[] = $row['title'];
    }
  }  



array(45) {
  [0]=>
  string(17) "32.79690170288086"
  [1]=>
  NULL
  [2]=>
  NULL
  [3]=>
  string(16) "32.7942008972168"
  [4]=>
  NULL
  [5]=>
  NULL
  [6]=>
  string(18) "32.711700439453125"
  [7]=>
  NULL
  [8]=>
  NULL
  [9]=>
  string(19) "-117.25499725341797"
  [10]=>
  NULL
  [11]=>
  NULL
  [12]=>
  string(19) "-117.25599670410156"
  [13]=>
  NULL
  [14]=>
  NULL
  [15]=>
  string(19) "-117.15699768066406"
  [16]=>
  NULL
  [17]=>
  NULL
  [18]=>
  string(27) "2$ off beers, kids eat free"
  [19]=>
  NULL
  [20]=>
  NULL
  [21]=>
  string(12) "Taco Tuesday"
  [22]=>
  NULL
  [23]=>
  NULL
  [24]=>
  string(16) "House Wine Night"
  [25]=>
  NULL
  [26]=>
  NULL
  [27]=>
  string(12) "Taco Tuesday"
  [28]=>
  NULL
  [29]=>
  NULL
  [30]=>
  string(24) "Backyard Kitchen and Tap"
  [31]=>
  NULL
  [32]=>
  NULL
  [33]=>
  string(23) "Pacific Beach Shoreclub"
  [34]=>
  NULL
  [35]=>
  NULL
  [36]=>
  string(13) "Knotty Barrel"
  [37]=>
  NULL
  [38]=>
  NULL
  [39]=>
  string(13) "Deals Near Me"
  [40]=>
  NULL
  [41]=>
  NULL
  [42]=>
  string(16) "Craft beer night"
  [43]=>
  NULL
  [44]=>
  NULL
}

EDIT: Jaypan helped me figure this out, here is the final code:

$result = \Drupal::database()->query('SELECT node.title, latitude.field_latitude_value, longitude.field_longitude_value '.
  'FROM {node_field_data} AS node '.
  'JOIN {node__field_latitude} AS latitude '.
  'ON latitude.entity_id = node.nid '.
  'JOIN {node__field_longitude} AS longitude '.
  'ON longitude.entity_id = node.nid '.                                          
  'JOIN {node__field_postal_code} AS zipcode '.
  'ON zipcode.entity_id = node.nid '.
  'WHERE zipcode.field_postal_code_value = '.$postal_code); 

Answer 1

If I understand you correctly, you want:

$data = \Drupal::database()->query(
  'SELECT node.title, latitude.field_latitude_value, longitude.field_longitude_value ' .
  'FROM {node_field_data} AS node ' .
  'JOIN {node__field_latitude} AS latitude ' .
  'ON latitude.entity_id = node.id ' .
  'JOIN {node__field_longitude} AS longitude ' .
  'ON longitude.entity_id = node.id ' .
  'WHERE node.nid = :nid',
  [':nid' => $nid]
)->fetchAssoc();

Set $nid to the node ID you want, and you should be good.