Return NULL results from IN (Sub-Query)

Databases
1

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?

Thanks,
Eric

2 

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

:smiley:

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

3

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
1
2
3

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.

Eric

4

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:

5

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 
    route.id as routeID
  , walls.id as wallsID
FROM walls
LEFT OUTER JOIN `drupal_relativity` AS route
ON route.parent_nid = walls.id
6

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:

7

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

8

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?

9

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?

10

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.

11

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:

SELECT 
	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:

12

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.

13

ON w.parent_nid =36

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

14

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.

15

So:

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

Right?

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:

16

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.

17

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

18

Thanks for your help! :slight_smile: