Query to select and combine uppermost and lowermost value from tables

Question

We have the following tables on our SQL Server 2012.

Table A (data):

ID, Description
---------------
1 , Bla 1
2 , Bla 2
3 , Bla 3

Table P (data):

ID, ParentID, Name
------------------
1 , NULL    , AAA
2 , 3       , CCC
3 , 1       , XXX

Table X (foreign keys A_ID to A.ID and P_ID to P.ID):

ID, A_ID, P_ID
--------------
1 , 1   , 1
2 , 1   , 2
3 , 2   , 1
4 , 2   , 2
5 , 2   , 3
6 , 3   , 1

Question:

We need a query something like:

SELECT ... 
WHERE A_ID = 1

which should return this result:

ID, Name, Subname
-----------------
2 , AAA , CCC

Name needs to contain the upper most Name from Table P, i.e. the one that has no ParentID.
Subname needs to contain the bottom most Name from the Table P for which the ID still exists in Table X.
ID needs to contain the ID from Table X where P_ID is the ID of the bottom most child.

Another example:

SELECT ... 
WHERE A_ID = 2

should return this result:

ID, Name, Subname
-----------------
4 , AAA , CCC

And

SELECT ... 
WHERE A_ID = 3

should return this result:

ID, Name, Subname
-----------------
6 , AAA , NULL

We've tried various queries, but some work only for 'where A_ID = 1' and not for 'where A_ID = 2'. In order to select the lowest level child from P, we've looked at the 'How to select lowest level in hierarchy form table post' which probably comes in handy for the query we're looking for.

A single query would be nice, but we will accept a stored procedure as well.

Thanks in advance!

Information

The ID columns in all tables are primary keys
The ID columns in any given table can be changed to any other value in the sample data, while taking into account the primary and foreign key constraints. (E.g. changing P.ID '2' to '4' also results in the change of X.P_ID's '2' to '4'.) This is to show that ID's are not necessarily in order.
Values in the column P.Name can be any non-null value.
Table P can have multiple rows with ParentId set to null.

Sample Data Taken from @NEER

DECLARE @A TABLE (ID INT, DESCRIPTION NVARCHAR(10))
INSERT INTO @A
VALUES  
(1 , 'Bla 1'),
(2 , 'Bla 2'),
(3 , 'Bla 3')


DECLARE @P TABLE (ID INT, ParentID INT, Name NVARCHAR(10))
INSERT INTO @P
VALUES  
(1 , NULL    , 'AAA'),
(2 , 3       , 'CCC'),
(3 , 1       , 'XXX')


DECLARE @X TABLE (ID INT,A_ID INT,P_ID INT)
INSERT INTO @X
VALUES  
(1 , 1   , 1),
(2 , 1   , 2),
(3 , 2   , 1),
(4 , 2   , 2),
(5 , 2   , 3),
(6 , 3   , 1)

Show the query you tried, and what was wrong with the results, so that we can help debug it. — Tab Alleman, Sep 20 '16 at 12:47
can you try the query I provided in my answer please, is it helpful? — Monah, Sep 20 '16 at 14:09

Unnikrishnan R · Answer 1 · 2016-09-21T05:13:30.987

2

Try with the below query. I think Table A is not required for getting the desired result.

SELECT TOP 1 First_VALUE(x.ID) OVER(ORDER BY x.ID desc)  ID
            ,First_VALUE(Name) OVER(ORDER BY p.ID) Name 
            ,CASE WHEN First_VALUE(Name) OVER(ORDER BY p.ID) =   First_VALUE(Name) OVER(ORDER BY p.ID desc) THEN NULL
                   ELSE First_VALUE(Name) OVER(ORDER BY p.ID desc) END   SubName
FROM [table P] p
 JOIN [table X] x
   ON p.ID=x.[P_ID]
WHERE x.[A_ID]=3

edited Sep 21 '16 at 05:13

answered Sep 20 '16 at 13:28

Unnikrishnan R

4,965
1
12
21

2

What if the Name value in P table at record 1 was 'XAA', and A_ID was 1, your query will return **2**,**BBB**, **XAA**? is this acceptable? you should not rely on `max(characters)` !!, this sample worked with you since the characters was sorted – Monah Sep 20 '16 at 13:55
2

What Hadi Hassan said is true. The data provided is just sample data and you cannot assume that everything will be in order as in the sample tables. I'll update the question in order to mention that. – Knots Sep 20 '16 at 14:00
1

We can't guess as to what your actual data looks like. If there are anomalies, do your best to provide accurate sample data – kjmerf Sep 20 '16 at 14:35

score 0 · Answer 2 · answered Sep 20 '16 at 13:31

Try following query

DECLARE @TableA AS TABLE (ID INT,Des NVARCHAR(MAX));
Insert Into @TableA VALUES(1,'Bal 1'); Insert Into @TableA VALUES(2,'Bal 2'); Insert Into @TableA VALUES(3,'Bal 3');
DECLARE @TableP AS TABLE (ID INT,ParentID INT,Name NVARCHAR(MAX));
Insert Into @TableP VALUES(1,Null,'AAA'); Insert Into @TableP VALUES(2,1,'BBB'); Insert Into @TableP VALUES(3,2,'CCC');
DECLARE @TableX AS TABLE (ID INT,A_ID INT,P_ID INT);
Insert Into @TableX Values(1,1,1); Insert Into @TableX Values(2,1,2); Insert Into @TableX Values(3,2,1); Insert Into @TableX Values(4,2,3); Insert Into @TableX Values(5,3,1);

Select Top 1 X.ID,(Select top 1 Name from @TableP Where ParentID is null) Name,P.Name as SubName  
from @TableX as X
Inner Join @TableP as P On P.ID=x.P_ID And P.ParentID IS Not Null
Where A_ID=1
Order by X.ID Desc

Select Top 1 X.ID,(Select top 1 Name from @TableP Where ParentID is null) Name,P.Name as SubName  
from @TableX as X
Left Join @TableP as P On P.ID=x.P_ID And P.ParentID IS Not Null
Where A_ID=2
Order by X.ID Desc

Select Top 1 X.ID,(Select top 1 Name from @TableP Where ParentID is null) Name,P.Name as SubName
from @TableX as X
Left Join @TableP as P On P.ID=x.P_ID And P.ParentID IS Not Null
Where A_ID=3 
Order by X.ID Desc

You also assume the data is provided in order, while in any table, the rows can be in any given order. While it does work for the example given, it will not work when we swap the values 2 and 3 in the 'P.ID' column. — Knots, Sep 20 '16 at 14:31

neer · Answer 3 · 2016-09-21T06:13:21.903

You can use Recursive CTE as the below:

DECLARE @A TABLE (ID INT, DESCRIPTION NVARCHAR(10))
INSERT INTO @A
VALUES  
(1 , 'Bla 1'),
(2 , 'Bla 2'),
(3 , 'Bla 3')


DECLARE @P TABLE (ID INT, ParentID INT, Name NVARCHAR(10))
INSERT INTO @P
VALUES  
(1 , NULL    , 'AAA'),
(2 , 3       , 'CCC'),
(3 , 1       , 'XXX')


DECLARE @X TABLE (ID INT,A_ID INT,P_ID INT)
INSERT INTO @X
VALUES  
(1 , 1   , 1),
(2 , 1   , 2),
(3 , 2   , 1),
(4 , 2   , 2),
(5 , 2   , 3),
(6 , 3   , 1)

DECLARE @A_ID INT = 2

;WITH Parents
AS
(
    SELECT
         P.ID, P.ParentID, P.Name
    FROM @P P WHERE P.ParentID IS NULL
    UNION ALL
    SELECT 
         P.ID, Parent.ID, Parent.Name
    FROM 
        @P P INNER JOIN 
        Parents Parent ON P.ParentID = Parent.ID
), Temp
AS
(
    SELECT
        X.ID,
        Parent.Name Name,
        IIF(P.ParentID IS NULL, NULL, P.Name) SubName       
    FROM
        @A A INNER JOIN 
        @X X ON X.A_ID = A.ID INNER JOIN 
        @P P ON X.P_ID = P.ID LEFT JOIN 
        Parents Parent ON P.ParentID = Parent.ID OR (P.ParentID IS NULL AND P.ID = Parent.ID)
    WHERE
        A.ID = @A_ID
), MainTable
AS
(
    SELECT 
        Temp.ID ,
        Temp.Name ,
        Temp.SubName,
        COUNT(Temp.ID) OVER (PARTITION BY Temp.Name ORDER BY (SELECT NULL)) CountOfRowByParent
    FROM 
        Temp
)


SELECT 
    MainTable.ID ,
    MainTable.Name ,
    MainTable.SubName      
FROM 
    MainTable
WHERE
    (
        MainTable.CountOfRowByParent > 1 AND
        MainTable.SubName IS NOT NULL 
    ) OR
    MainTable.CountOfRowByParent = 1

Result for 2:

ID  Name    SubName
4   AAA     CCC
5   AAA     XXX

This look very similar to what we tried (I'll add our query to the original question.) The problem with this is that you assume all names in table P are in order. If you change e.g. BBB to ZZZ, than the output will be wrong. — Knots, Sep 20 '16 at 14:11
I've edited my post and added the declares. (Thanks) The changes are made in those declares. — Knots, Sep 20 '16 at 15:03

score 0 · Answer 4 · answered Sep 20 '16 at 13:45

0

You can try the following

with report as (
   select max(x.ID) as ID, min(x.P_ID) as MinP, max(x.P_ID) as MaxP
   from X x
   where x.A_ID = 1 -- <-- here you can change the value
)

select r.ID,
       mn.Name as Name, 
       case when r.MinP = r.MaxP then null else  mx.Name end as Subname
from report r
inner join P mn on mn.ID = r.MinP
inner join P mx on mx.ID = r.MaxP

Hope this will help you

answered Sep 20 '16 at 13:45

Monah

6,714
6
22
52

Here you assume the ID's are in order, while in any table they can be in given order. When we swap the values 1 and 2 in the column 'P.ParentID', the query does not give a correct result. – Knots Sep 20 '16 at 14:23
the query doesn't rely on ParentID, if you mention the role of the ParentID, i will modify my answer – Monah Sep 20 '16 at 14:24
@Knots ok, you edited your question, I will have a look again on it now – Monah Sep 20 '16 at 14:27
@Knots clarification please, table P might contains many null parents? or just one which is treated as root? – Monah Sep 20 '16 at 14:31
There can be multiple rows with the ParentId set to null. – Knots Sep 20 '16 at 14:46

score 0 · Answer 5 · answered Sep 20 '16 at 15:07

0

Try it with a GROUP BY:

SELECT x.a_id, max(x.id) AS id, min(p.name) AS name, 
CASE WHEN max(p.name) = min(p.name) THEN NULL 
ELSE max(p.name) END AS subname
FROM p INNER JOIN x
ON p.id = x.p_id
GROUP BY x.a_id
HAVING x.a_id = 1

Still works with your updated sample data. Tested here: http://sqlfiddle.com/#!9/99597f/1

answered Sep 20 '16 at 15:07

kjmerf

4,275
3
21
29

When settings x.a_id to 2 it gives me the wrong Subname. Again: max's and min's are unlikely to be part of the answer, as the extra information states that rows can be in any order, id's could be swapped and names can be any non-null value. – Knots Sep 20 '16 at 15:14
Did you try the link? It works for me there. Like I said above, you have to give us representative sample data..... – kjmerf Sep 20 '16 at 15:28

Query to select and combine uppermost and lowermost value from tables

5 Answers5