[SQL] Firebird SQL using the "WITH" statement: Selection seems not correct when using the WITH...

I need to build an SQL query for Firebird 2.1. and I'm trying to use the WITH statement for a select, which needs to be executed multiple times within this query (I did never use this construct before).

The situation is (very simplified) as follows: I have a table, let's say ITEM_GROUP, which stores groups of items. Groups can be subgroups of each other. So there is another table CHILD_PARENT_GROUP, which stores the information, which group is the child of which. And lastly, each group can have some rule related to it, so there is a table GROUP_RULE, which stores the information, for which group I have which rules. Now, based on a given group id I need to find all parents and their rules (if they have rules).

The (simplified) SQL I am using is as follows:

WITH
GROUP_RULE AS(
select gr.ID AS GROUP_ID, rule.RULE_NAME AS RULE_NAME, childParent.PARENT_GROUP_ID AS PARENT_ID
from ITEM_GROUP gr
left join GROUP_RULE rule on gr.ID = rule.GROUP_ID
left join CHILD_PARENT_GROUP childParent on childParent.CHILD_GROUP_ID = gr.ID
)
SELECT
G1.GROUP_ID AS G1_ID, G1.RULE_NAME AS G1_RULE,
G2.GROUP_ID AS G2_ID, G2.RULE_NAME AS G2_RULE,
G3.GROUP_ID AS G3_ID, G3.RULE_NAME AS G3_RULE,

FROM GROUP_RULE G1
left join GROUP_RULE G2 on G1.PARENT_ID = G2.GR_ID
left join GROUP_RULE G3 on G2.PARENT_ID = G3.GR_ID

where G1.GR_ID in ('THE_PARENT_GROUP', 'SOME_CHILD_1', 'SOME_CHILD_2')


Let's say SOME_CHILD_2 is the child of SOME_CHILD_1, which is the child of THE_PARENT_GROUP. And let's say only the group with the id THE_PARENT_GROUP has a related rule (which's name is, for example, PARENT_RULE), all the other groups don't have rules. Now, the result I am getting from this query is quite strange: When THE_PARENT_GROUP is selected as first group, its rule is selected correctly, but if THE_PARENT_GROUP is selected as second or third group, null is selected as its rule (though it's id is selected correctly). So the result looks like this:



Does anybody know, why I am getting this result? Why is the ID of the parents displayed correctly, but the rule is selected only for the first child? Do I have some basic misunderstanding of the WITH statement? I am pretty sure, there are no typos in my SQL, I have looked over it 100 times already.

Continue reading...