2

I've got two tables, Foo and Bar. Foo and Bar can be joined by matching Foo's "nid" column to Bar's "col" column. In use, some rows in Foo will have a corresponding entry in Bar, but some won't. I now want to get all the rows in Foo that do NOT have a related entry in Bar.

I'm trying something like this:

$the_query = db_select('foo', 'f');
$the_query->fields('f', array('nid', 'val'));
$the_query->join('bar', 'b', 'f.nid = b.col');
$the_query->isNull('b.col');
$the_query->execute()...

but it's not returning anything; my method of testing for null-ness is almost certainly wrong. I've never been good at these kinds of queries, let alone being able to figure out how to do it within the context of the DB api. Any clues out there? Thanks!

1 Answer 1

3

If I am reading this right, I think you need to use a left join and then test the nullness.

$the_query = db_select('foo', 'f');
  ->fields('f', array('nid', 'val'))

$the_query->leftJoin('bar', 'b', 'f.nid = b.col');

$results = $the_query->isNull('b.col');
  ->execute()
  ->fetchAll();

Very often, though, I will break down and do this with two queries. The first will get a list of nids, the second will use a NOT IN condition.

1
  • Ah, so it was a leftjoin... Got it (yes, it's working in my code); thanks much!
    – Jim Miller
    May 23, 2014 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.