Select Single Row from Child Table for Each Row in Parent Table

SELECT single row from child table for each row in parent table

"get only one row from child table for each parent row with child fields included"

That sounds like the child table can have more than one row for the same pID value. And you want only one child row for each pID.

SELECT pID, Min(cID) AS MinOfcID
FROM child
GROUP BY pID;

Join that GROUP BY query back to the child table again to retrieve the other columns for each target cID value. Save this query as qryChild.

SELECT
    c.pID,
    c.cID,
    c.phone,
    c.company,
    c.title,
    c.address
FROM
    (
        SELECT pID, Min(cID) AS MinOfcID
        FROM child
        GROUP BY pID
    ) AS map
    INNER JOIN child AS c
    ON c.cID = map.MinOfcID;

Finally, to include lastname values, join the parent table to qryChild.

How to select rows in parent table based on a value in child table

Assuming that when you say "When I query for Warehouse X" you are running individual queries for a single warehouse, just a simple INNER JOIN and a WHERE clause should do it:

SELECT DISTINCT o.OrderNo, o.OrderDt, o.Status, o.Type
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderNo = oi.OrderNo
WHERE Warehouse = 10;

SELECT DISTINCT o.OrderNo, o.OrderDt, o.Status, o.Type
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderNo = oi.OrderNo
WHERE Warehouse = 20;

If you want to have everything in one result set add ItemCode and Warehouse to the query and leave out the WHERE clause.

SELECT
    o.OrderNo,
    o.OrderDt,
    o.Status,
    o.Type,
    oi.ItemCode,
    oi.Warehouse
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderNo = oi.OrderNo;

SQL - 1 Parent Table, 2 Child Tables - return single row for each row in the child table

declare @ParentID int
set @ParentID = 1

select a.name,
       bc.descb,
       bc.descc
from   TableA as a
  cross join (select b.descb,
                     c.descc
              from   (select *,
                             row_number() over(order by b.bkey) as rn
                      from   TableB as b
                      where  b.parentid = @parentid) as b
                full outer join 
                     (select *,
                             row_number() over(order by c.ckey) as rn
                      from   TableC as c
                      where  c.parentid = @parentid) as c
                  on b.rn = c.rn) as bc
where  a.parentid = @parentid  

Try here: https://data.stackexchange.com/stackoverflow/qt/112538/

Edit: A version using ExternalKey to query multiple ParentID's

Suggested indexes:

create index IX_B_ParentID on TableB(ParentID) include (DescB)
create index IX_C_ParentID on TableC(ParentID) include (DescC)

I would create a table variable that holds the ParentID's that matches the ExternalKey and then use that instead of TableA in the query.

declare @ExternalKey int = 1

declare @T table(ParentID int primary key, Name varchar(20))
insert into @T (ParentID, Name)
select ParentID, NAme
from TableA
where ExternalKey = @ExternalKey

select a.name,
       bc.descb,
       bc.descc
from   @T as a
  inner join (select b.descb,
                     c.descc,
                     coalesce(b.ParentID, c.ParentID) as ParentID
              from   (select b.ParentID,
                             b.DescB,
                             row_number() over(partition by b.ParentID order by b.bkey) as rn
                      from   TableB as b
                      where  b.parentid in (select ParentID from @T)) as b
                full outer join
                     (select c.ParentID,
                             c.DescC,
                             row_number() over(partition by c.ParentID order by c.ckey) as rn
                      from   TableC as c
                      where  c.parentid in (select ParentID from @T)) as c
                  on b.rn = c.rn and
                     b.ParentID = c.ParentID) as bc
    on a.ParentID = bc.ParentID

Get all parent rows and each row followed by their child's rows

The overall plan is to have parent join (parent + child) order by (parent ID, child ID)

SELECT
    c.level_id,
    c.level_name,
    c.level_code,
    CASE WHEN c.is_child = 1 THEN c.parent_id END AS parent_id,
FROM
    mainLevel p
    INNER JOIN (
    SELECT level_id, level_name, level_code, parent_id, 1 AS is_child
    FROM subLevel
    UNION ALL
    SELECT level_id, level_name, level_code, level_id, 0 AS is_child
    FROM mainLevel
    ) c on p.level_id = c.parent_id
ORDER BY p.level_id, is_child, c.level_id

Additional version to adopt to the newly clarified column availability

SELECT
    w.name,
    w.id,
    CASE WHEN w.is_child = 1 THEN w.mid END AS parent_id
FROM
    Mainlevel m
    INNER JOIN (
    SELECT id, name, parentID AS mid, 1 AS is_child
    FROM Sublevel
    UNION ALL
    SELECT id, name, id AS mid, 0 AS is_child
    FROM Mainlevel
    ) w on m.id = w.mid
ORDER BY  m.id, is_child, w.id

Pick one row for each pair of parent-child and child-parent relationships

Sample data

DECLARE @Dependencies TABLE
([ID] int, [parent] varchar(50), [dependent] varchar(50), [relationship] varchar(50));

INSERT INTO @Dependencies
([ID], [parent], [dependent], [relationship])
VALUES
(1234, 'John', 'Mike', 'Parent'),
(1235, 'Mike', 'John', 'Child'),
(1236, 'Nancy', 'John', 'Spouse'),
(1237, 'John', 'Nancy', 'Spouse'),
(1238, 'Peter', 'Mike', 'Sibling'),
(1239, 'Mike', 'Peter', 'Sibling');

Query

Calculate MIN and MAX of (parent, dependent) and then you can group them together.

SELECT
    ID
    ,CASE WHEN [parent] < [dependent] THEN [parent] ELSE [dependent] END AS MinRelationship
    ,CASE WHEN [parent] > [dependent] THEN [parent] ELSE [dependent] END AS MaxRelationship
    ,[relationship]
FROM @Dependencies
;

Result

+------+-----------------+-----------------+--------------+
|  ID  | MinRelationship | MaxRelationship | relationship |
+------+-----------------+-----------------+--------------+
| 1234 | John            | Mike            | Parent       |
| 1235 | John            | Mike            | Child        |
| 1236 | John            | Nancy           | Spouse       |
| 1237 | John            | Nancy           | Spouse       |
| 1238 | Mike            | Peter           | Sibling      |
| 1239 | Mike            | Peter           | Sibling      |
+------+-----------------+-----------------+--------------+

The rest depends on which row from each pair you want to choose. For example, we may want to choose a row with the smallest ID. CTE_MinMax is the simple query above. CTE_rn adds a number to each row partitioned by the pair and ordered by ID. The final SELECT returns only one row for each pair.

The query will work correctly if there is only one entry (not a pair), or if there are more than 2 entries.

WITH
CTE_MinMax
AS
(
    SELECT
        ID
        ,CASE WHEN [parent] < [dependent] THEN [parent] ELSE [dependent] END AS MinRelationship
        ,CASE WHEN [parent] > [dependent] THEN [parent] ELSE [dependent] END AS MaxRelationship
        ,[relationship]
    FROM @Dependencies
)
,CTE_rn
AS
(
    SELECT
        ID
        ,MinRelationship
        ,MaxRelationship
        ,relationship
        ,ROW_NUMBER() OVER (PARTITION BY MinRelationship, MaxRelationship ORDER BY ID) AS rn
    FROM CTE_MinMax
)
SELECT
    ID
    ,MinRelationship
    ,MaxRelationship
    ,relationship
FROM CTE_rn
WHERE rn = 1
;

Result

+------+-----------------+-----------------+--------------+
|  ID  | MinRelationship | MaxRelationship | relationship |
+------+-----------------+-----------------+--------------+
| 1234 | John            | Mike            | Parent       |
| 1236 | John            | Nancy           | Spouse       |
| 1238 | Mike            | Peter           | Sibling      |
+------+-----------------+-----------------+--------------+

Get latest child row with parent table row

You can use the Postgres extension distinct on:

select distinct on (p.id) p.* c.*
from posts p left join
     comments c
     on p.id = c.post_id
order by p.id desc, c.created_at desc
limit 10;

This sorts the data by the order by clause, returning the first row based on the keys in the distinct on.

---

Related Topics