Return NULL results from IN (Sub-Query)

I can’t seem to return NULL results from the following query:

SELECT route.*
FROM `drupal_relativity` AS route
WHERE route.parent_nid IN 
	SELECT wall.nid 
	FROM `drupal_relativity` AS wall 
	WHERE wall.parent_nid = 40

The drupal_relativity table is just a join table holding values of parent and child relationships.

The sub-query returns 3 wall.nid’s to be used in the parent query’s IN() statement. But, only 1 wall.nid has a route therefore only 1 result is returned.

How can I return all 3 walls even if they do not have any child routes?


SELECT wall.nid
FROM `drupal_relativity` AS wall
WHERE wall.parent_nid = 40


Just kidding. If you could explain what you’re trying to do, it’ll be easier to help you.

OK, let me explain better:

I have 2 types of content: Walls & Routes.

Walls can have many Routes. Routes belong to one wall. This parent-child relationship is stored in a join table (drupal_relativity) which contains the parent and child ID fields (nid, parent_nid).

For example:

Wall IDs

Join Table (drupal_relativity): Route ID - Parent Wall ID
4 - 1
5 - 2
6 - 2

What I want is to query the drupal_relativity table and return all IDs for Walls and all ID’s for Routes which exist in each Wall.

The query I posted above will return Wall IDs and all Route IDs for each Wall but Walls that do not have Routes are not returned. I want all Walls to be returned whether or not they have Routes. From what I understand, Walls without Routes are not being returned because they have a NULL value for Routes.

For the example above, my query would return:

4 - 1
5 - 2
6 - 2

But I want it to return:

4 - 1
5 - 2
6 - 2
NULL - 3

I hope that makes sense.


I think I got it using LEFT JOINs instead of sub-queries:

SELECT w.nid AS `wallid` , r.nid AS `routeid`
FROM `drupal_relativity` AS a
LEFT JOIN `drupal_relativity` AS w ON w.parent_nid =40
LEFT JOIN `drupal_relativity` AS r ON r.parent_nid = w.nid
GROUP BY r.nid

Does that look about right? It seems to be returning the right info…:shifty:

I’m not sure I have your DB design completely clear. For instance, do you have ‘walls’ and ‘routes’ tables as well?

Let’s assume you have a ‘walls’ table. Then the query would be something like:

SELECT as routeID
  , as wallsID
FROM walls
LEFT OUTER JOIN `drupal_relativity` AS route
ON route.parent_nid =

Left join is certainly the way to go. I really have no idea how your table works, but hey, if you get the right info then that’s ok :smiley:

I have a “node” table which holds both Walls and Routes. The join “drupal_relativity” handles their relationships. Does that sound about right?

I wonder how you get the walls without children from the relativity table. Do all walls exist in that table, even if they haven’t got any routes?

Ow! My head is spinning… (:
I have 2 tables:
drupal_node” which stores each piece of content on the site. It could be Routes, Walls or other content. Their primary key is “nid”.

drupal_relativity” stores the hierarchical relationship between records in the “drupal_node” table. There are only 2 fields in this table: nid, parent_nid.

I guess I should have made all of this clear from the beginning but does it make sense now?

Yes it does.
But I still don’t understand how you can get all the walls, including the ones without routes, querying only the relativity table.

I guess it’s because of the left joins… I really don’t know but it seems to be working. :slight_smile:

Here’s my query now:

	wallnode.title as `walltitle`,
	routenode.title as `walltitle`,
	w.nid AS `wallid`, 
	r.nid AS `routeid`
FROM `drupal_relativity` AS a
LEFT JOIN `drupal_relativity` AS w 
	ON w.parent_nid =36
LEFT JOIN `drupal_relativity` AS r 
	ON r.parent_nid = w.nid
LEFT JOIN `drupal_node` AS `wallnode` 
	ON wallnode.nid = w.nid
LEFT JOIN `drupal_node` AS `routenode` 
	ON routenode.nid = r.nid
GROUP BY r.nid

And here’s what it returns:

And this is what it returns if a Wall has 0 Routes:

My field names are messed up in those 2 screen captures. The 2nd walltitles column should be routetitles.

ON w.parent_nid =36

What does this mean? In your logic of walls and routes I mean?

Ahh… that’s the Wall’s Parent. I didn’t want to complicate things in my post but I have a 3 level hierarchy:

Areas > Walls > Routes

36 is the Area’s ID. But that value is a known value. Now I can take that ID and drill down to get this area’s walls and then get this area’s wall’s routes.


One area can have many walls, and each wall only 1 area
One wall can have many routes, and each route only 1 wall


In that case you don’t need the relativity table, a parentid column in the nodes table is sufficient.
Then left join the nodes table twice to itself and you’re done :slight_smile:

Good point! But, I’m working within the constraints of Drupal and I’m not sure how, and not sure if it’s a good idea, to modify a core Drupal table. For now, I think I have to live with the join table… which is cool with me.

No, modifying a core table doesn’t sound like a good idea.

Thanks for your help! :slight_smile: