SELECT inherit values from Parent (self-references)

Question

I have 02 tables Rubrique and BulletinRubrique the table Rubrique contain the following columns :

ID int not null,
Name, varchar(max) not null,
RubriqueA_ID int null,
RubriqueB_ID int null

with data for example :

+-------+---------+--------------+--------------+
| ID    | Name    | RubriqueA_ID | RubriqueB_ID | 
+-------+---------+--------------+--------------+
| 1     | R1      |    2         |    3         |
| 2     | R2      |    1         |    2         |
| 3     | R3      |    NULL      |    NULL      |
| 4     | R4      |    NULL      |    6         |
| 5     | R5      |    6         |    NULL      |
| 6     | R6      |    NULL      |    1         |
+-------+---------+--------------+--------------+

the two columns RubriqueA_ID, RubriqueB_ID are the foreigns Keys for the same table Rubrique (self-referencing) and they might be NULL

For the table BulletinRubrique :

ID int not null,
EmployeID int not null,
RubriqueID int not null,
Value float not null

with data :

+-------+-----------+--------------+------------+
| ID    | EmployeID | Rubrique_ID  | Value      | 
+-------+-----------+--------------+------------+
| B1    | EMP1      | 1            | 150        |
| B1    | EMP1      | 2            | 220        |
| B1    | EMP1      | 3            | 195        |
| B1    | EMP1      | 4            | 170        |
| B1    | EMP1      | 5            | 320        |
| B1    | EMP1      | 6            | 745        |
+-------+-----------+--------------+------------+

What I am trying to do is bulding the sql query to get result as :

+-------+-----------+--------------+------------+----------+---------+
| ID    | EmployeID | Rubrique_ID  | Value      | A_Value  | B_Value |
+-------+-----------+--------------+------------+----------+---------+
| B1    | EMP1      | 1            | 150        | 220      | 195     |
| B1    | EMP1      | 2            | 220        | 150      | 220     |
| B1    | EMP1      | 3            | 195        | NULL     | NULL    |
| B1    | EMP1      | 4            | 170        | NULL     | 745     |
| B1    | EMP1      | 5            | 320        | 745      | NULL    |
| B1    | EMP1      | 6            | 745        | NULL     | 150     |
+-------+-----------+--------------+------------+----------+---------+

Please help me to build this query and thanks

Dr. X · Accepted Answer · 2017-08-21T21:38:34.830

3

http://rextester.com/FIJE42564 is the working code

SELECT t2.ID, t2.EmployeID, t2.RubriqueID, t2.Value, t2.A_Value, r2.Value AS B_value  FROM BulletinRubrique r2 
 RIGHT JOIN
   (
    SELECT t1.ID, t1.EmployeID, t1.RubriqueID, t1.Value, r1.VALUE AS A_VALUE, t1.B_VALUE  FROM BulletinRubrique r1 
      RIGHT JOIN ( 
        SELECT b.ID, b.EmployeID,b.RubriqueID, b.Value, r.RubriqueA_ID AS A_Value,  r.RubriqueB_ID AS B_Value
        FROM BulletinRubrique b RIGHT JOIN Rubrique r ON r.ID = b.RubriqueID ) t1 
      ON t1.A_Value =  r1.RubriqueID
   )t2 
      ON t2.B_Value =  r2.RubriqueID
      ORDER BY t2.RubriqueID

edited Aug 21 '17 at 21:38

answered Aug 21 '17 at 20:26

Dr. X

2,890
2
15
37

1

In BulletinRubrique table EmployeID and RubriqueID can not be int because in your code they are nvarchar(2) and nvarchar(4) – Dr. X Aug 21 '17 at 21:40
You are welcome. It is a very advanced question... Have a nice coding... – Dr. X Aug 21 '17 at 21:49

Rex · Answer 2 · 2017-08-22T17:54:34.720

1

Being a fan of nested CTE, this is the way I tried solving this

WITH CTE1 as(
SELECT 
    BR.Id, BR.EmployeID, BR.RubriqueID, BR.value, R1.Rubriquea_Id, R2.Rubriqueb_Id 
FROM 
    BulletinRubrique BR
LEFT JOIN 
    Rubrique R1 ON R1.Id = BR.RubriqueID
LEFT JOIN 
    Rubrique R2 ON  R2.Id = BR.RubriqueID
),
CTE2 as (
SELECT 
    C1.Id, C1.EmployeID, C1.RubriqueID, C1.value, C2.value as A_Value, C3.value as B_Value 
FROM 
    CTE1 C1
LEFT JOIN 
    CTE1 C2 ON C1.Rubriquea_Id = C2.RubriqueID
LEFT JOIN 
    CTE1 C3 ON  C1.RubriqueB_Id = C3.RubriqueID
)
SELECT C1.Id, 
    C1.EmployeID, 
    C1.RubriqueID, 
    C1.value,
    C1.A_Value as A_Value, 
    C1.B_Value as B_Value  FROM CTE2 C1
ORDER BY Id

edited Aug 22 '17 at 17:54

answered Aug 22 '17 at 13:17

Rex

521
3
8

I tested your query and it didn't give the desired output, for each row : Value_A = Value_B = Value, and there is no null value for the columns Value_A and Value_B, Thank you very match for trying helping me – cheikh17 Aug 22 '17 at 17:37
My bad. Last select statement had values fetching from same column(for A_Value and B_Value). I updated it now. – Rex Aug 22 '17 at 17:55
It is working, BRAVO my friend, thank you, One more question PLZ, In order to get the result quicker, which method is more performant and optimized : using the CTE or Sub-Query like used in the first answer. – cheikh17 Aug 22 '17 at 18:55
Well, it depends on the indexes that these tables has. With no index when I looked execution plan for both queries, query from @Dr. Geek looks lot better than mine. – Rex Aug 22 '17 at 19:08
Thank you my friend, you helped me alot. – cheikh17 Aug 22 '17 at 19:43

SELECT inherit values from Parent (self-references)

2 Answers2