SQL Server Return a result set of products, their parents and children

4zcjmb1e 于 2023-06-21 发布在其他

关注(0)|答案(3)|浏览(128)

I have a standard bunch of products with a normal parent/child relationship table in a SQL Server database.

There are a set of products which have been modified, I want to be able to get these and their children and parents in a single record set, so I can perform some maintenance on their records. I know I could do this as 3 queries and UNION them but I was wondering if there was a better way to get them?

products TABLE

pid        mod_date
----------------------
1          01-01-2023
2          20-01-2023
3          09-06-2023
4          01-01-2023
5          20-05-2023
6          09-06-2023
7          01-01-2023

parent_child TABLE

parent_pid   child_pid 
----------------------
1           2
3           4
5           6
5           7

With the following SQL

SELECT pid 
FROM products 
WHERE mod_date > dateAdd(day, -1, getdate())

UNION
SELECT pc1.child_pid 
FROM products p 
INNER JOIN parent_child pc1
ON pc1.parent_pid = p.pid
WHERE p.mod_date > dateAdd(day, -1, getdate())

UNION
SELECT pc2.parent_pid 
FROM products p 
INNER JOIN parent_child pc2
ON pc2.child_pid = p.pid
WHERE p.mod_date > dateAdd(day, -1, getdate())

(Assume that getdate() returns 2023-06-09)

Results:

Is there a more succinct way to get the same results, particularly when my where condition could get much more complicated (but would always be the same for each select statement)

sql-server

来源：https://stackoverflow.com/questions/76438690/return-a-result-set-of-products-their-parents-and-children

3条答案

按热度按时间

puruo6ea1#

You could shorten it a bit using CTE and make union once:

with p as (select pid from products where mod_date > dateAdd(day, -1, getdate()))
select pid from p union
select case p.pid when pc.parent_id then child_id else parent_id end 
from p join parent_child pc on p.pid in (parent_id, child_id)

dbfiddle demo

赞(0）回复(0）举报 2023-06-21

col17t5w2#

You can move the union part to an apply:

SELECT DISTINCT x.pid 
FROM products p
cross apply (
  select pid
  union ALL
  SELECT pc1.child_pid 
  FROM parent_child pc1
  where pc1.parent_pid = p.pid
  UNION ALL
  SELECT pc2.parent_pid 
  FROM parent_child pc2
  WHERE pc2.child_pid = p.pid
) x
WHERE mod_date > dateAdd(day, -1, getdate())

赞(0）回复(0）举报 2023-06-21

cld4siwp3#

Try the following using a left join and the exists operator:

DECLARE @dt DATE = '2023-06-09'; -- You may replace this fixed date with GETDATE()

WITH mod_products AS
( -- Do a left join to get the parents and children of the modified products
  SELECT P.pid, PC.parent_pid, PC.child_pid
  FROM products P LEFT JOIN parent_child PC
  ON P.pid IN (PC.parent_pid, PC.child_pid)
  WHERE mod_date > DATEADD(day, -1,  @dt)
)
-- Use the exists operator to get the products that exist in the modified products or their (parents and children)
SELECT P.pid FROM products P
WHERE 
  EXISTS
    (
      SELECT 1 FROM mod_products MP
      WHERE P.pid IN (MP.pid, MP.parent_pid, MP.child_pid)
    )

demo

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Return a result set of products, their parents and children

3条答案

相关问题

热门标签

最新问答