CODEWITHSUNDEEP
phpmysqlsql
Morgan Delaney
Morgan Delaney

Reputation: 2439

MySQL LEFT JOIN where joined table is null

My current SQL is:

SELECT * FROM `node`
LEFT JOIN `field_data_field_area_of_study_category`
ON `field_data_field_area_of_study_category`.`entity_id` = `node`.`nid`
WHERE `node`.`type` = 'area_of_study'
GROUP BY `node`.`nid`

For now, to make it work I run a foreach loop to is_null() on the column.

I'm trying to emulate the is_null() check within the SQL query.

Thanks for reading.

EDIT This is the foreach loop that runs the is_null(). I think a better way to ask my question would be: how do make the SQL return only rows from the node table where there is no matching field_data_field_area_of_study_category table?

foreach($aos_nodes as $aos_node):
  if (is_null($aos_node->field_area_of_study_category_tid)):
    $menu_item_display[$aos_node->title] = array(
      'id' => $aos_node->nid,
      'type' => 'node',
      'children_markup' => '',
      'icon' => '',
      'name' => $aos_node->title,
      'href' => drupal_get_path_alias('node/'.$aos_node->nid),
    );
  endif;
endforeach;

Upvotes: 0

Views: 274

Answers (1)

Eric
Eric

Reputation: 95133

To return the rows where there isn't a matching one:

SELECT * FROM `node`
LEFT JOIN `field_data_field_area_of_study_category`
ON `field_data_field_area_of_study_category`.`entity_id` = `node`.`nid`
WHERE `node`.`type` = 'area_of_study' 
    and `field_data_field_area_of_study_category`.`entity_id` is null
GROUP BY `node`.`nid`

Additionally, you can use not exists:

select
    *
from
    node n
where
    n.type = 'area_of_study'
    and not exists (
        select 
            1 
        from 
            field_data_field_area_of_study_category f 
        where 
            f.entity_id = n.nid
    )

Upvotes: 5

Related Questions