Query to select and combine the highest and lowest values ​​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 for A.ID and P_ID for 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 request:

SELECT ... 
WHERE A_ID = 1

which should return this result:

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

• The name must contain the uppermost Name from table P, that is, a name that does not have a ParentID.
• The name should indicate the lowest name from table P, for which the identifier still exists in table X.
• The ID must contain the identifier from table X, where P_ID is the identifier of the lowest 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

, " A_ID = 1", " A_ID = 2". P, , , , , .

, .

!

• , . (, P.ID '2' '4' X.P_ID '2' '4'.) , .
• P.Name .
• P ParentId .

@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)

Answer 1

. , Table A .

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

Answer 2

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

,

Answer 3

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

Answer 4

Recursive CTE :

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

2:

ID  Name    SubName
4   AAA     CCC
5   AAA     XXX

Answer 5

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 working with your updated sample data. Tested here: http://sqlfiddle.com/#!9/99597f/1