Geek asks and answers

Invoke a T-SQL recursive query

Here is my script. Let's say I have two tables "Car" and "Karpart". A car consists of many parts, and each part can belong to several cars. One of the complications in my case is that each part gets a new PartID, even if it's the same name, but it just belongs to a different car. This is something that I can’t control, so bear with me. Here is the script to configure.

IF OBJECT_ID('Car') IS NOT NULL DROP TABLE Car
CREATE TABLE Car (
CarID INT,
CarName VARCHAR(16)
)

IF OBJECT_ID('CarPart') IS NOT NULL DROP TABLE CarPart
CREATE TABLE CarPart (
PartID INT,
PartName VARCHAR(16),
CarID INT
)

INSERT INTO Car
VALUES (1, 'Chevy'),
    (2, 'Ford'),
    (3, 'Toyota'),
    (4, 'Honda'),
    (5, 'Nissan'),
    (6, 'Hugo')

INSERT INTO CarPart 
VALUES  (110, 'Engine', 1),
  (120, 'Engine', 2),
  (210, 'Door', 1),
  (220, 'Door', 3),
  (310, 'Seat', 4),
  (320, 'Seat', 5),
  (410, 'Window', 3),
  (510, 'Wheel', 2),
  (420, 'Window', 6)

As you can see, the Engine part belongs to both Chevy and Ford and is listed twice with different identifiers. Once again, this is a design limitation that I have to live with.

: , , , . , . : @StartCar → @StartCar → → "" → , "" - → , .

, :

DECLARE @StartCar VARCHAR(16) = 'Chevy'

;WITH cte (CarName, PartName)
AS
(
SELECT c.CarName,
       cp.PartName
FROM CarPart cp
JOIN Car c ON cp.CarID = c.CarID
WHERE c.CarName = @StartCar
UNION ALL
SELECT c.CarName,
       cp.PartName
FROM CarPart cp
JOIN Car c ON cp.CarID = c.CarID
JOIN cte cte ON cp.PartName = cte.PartName
)
SELECT CarName, PartName
FROM cte

. , :

CarName PartName
Chevy Engine
Chevy Door
Ford Engine
Ford Wheel
Toyota Door
Toyota Window
Hugo Window

.

!

+5
4

, , . - . , . SQL Server HIERARCHYID .

CTE . , , .

WITH cte(CarID,hier) AS (
  SELECT CarID, CAST('/'+LTRIM(CarID)+'/' AS varchar(max))
  FROM Car
  WHERE CarName = @StartCar

  UNION ALL

  SELECT c2.CarID, hier+LTRIM(c2.CarID)+'/'
  FROM Car AS c
  JOIN cte ON cte.CarID = c.CarID
  JOIN CarPart AS c1 ON c.CarID = c1.CarID
  JOIN CarPart AS c2 ON c2.PartName = c1.PartName
  WHERE hier NOT LIKE '%/'+LTRIM(c2.CarID)+'/%'
)

SELECT
  c.CarName, cp.PartName
FROM Car AS c
JOIN CarPart AS cp ON cp.CarID = c.CarID
JOIN cte on cte.CarID = c.CarID
+2

SQL Fiddle

1:

declare @t table (
  car_name varchar(100), 
  part_name varchar(100)
  )

declare @car int = 3

insert @t
select c.CarName, p.PartName
from Car c join CarPart p on c.CarID = p.CarID
where c.CarID = @car


while exists(
  select c.CarName, p.PartName
  from Car c join CarPart p on c.CarID = p.CarID
  where c.CarName in (
    select c.CarName
    from Car c join CarPart p on c.CarID = p.CarID
    where p.PartName in (select part_name from @t)
      and c.CarName not in (select car_name from @t)
    )
) 
insert @t
  select c.CarName, p.PartName
  from Car c join CarPart p on c.CarID = p.CarID
  where c.CarName in (
    select c.CarName
    from Car c join CarPart p on c.CarID = p.CarID
    where p.PartName in (select part_name from @t)
      and c.CarName not in (select car_name from @t)
    )

select * from @t

:

| CAR_NAME | PART_NAME |
------------------------
|   Toyota |      Door |
|   Toyota |    Window |
|    Chevy |    Engine |
|    Chevy |      Door |
|     Hugo |    Window |
|     Ford |    Engine |
|     Ford |     Wheel |
+2

, cte , , . , , .

, . cte car_hierarchy , CarID, , , . . ( , , .)

- . , . , . car_left car_right .

- :

IF OBJECT_ID('dbo.Car') IS NOT NULL DROP TABLE dbo.Car
CREATE TABLE dbo.Car (
CarID INT,
CarName VARCHAR(16)
)

IF OBJECT_ID('dbo.CarPart') IS NOT NULL DROP TABLE dbo.CarPart
CREATE TABLE dbo.CarPart (
PartID INT,
PartName VARCHAR(16),
CarID INT
)

INSERT INTO dbo.Car
VALUES (1, 'Chevy'),
    (2, 'Ford'),
    (3, 'Toyota'),
    (4, 'Honda'),
    (5, 'Nissan'),
    (6, 'Hugo')

INSERT INTO dbo.CarPart 
VALUES  (110, 'Engine', 1),
  (120, 'Engine', 2),
  (210, 'Door', 1),
  (220, 'Door', 3),
  (310, 'Seat', 4),
  (320, 'Seat', 5),
  (410, 'Window', 3),
  (510, 'Wheel', 2),
  (420, 'Window', 6)


DECLARE @StartCarID INT = 1;

WITH 
car_hierachy (CarID1, CarID2) AS (
  SELECT DISTINCT
         cp1.CarID CarID1,
         cp2.CarID CarID2
  FROM dbo.CarPart cp1
  JOIN dbo.CarPart cp2
  ON cp1.PartName = cp2.PartName
  AND cp1.CarID < cp2.CarID
),
car_left(CarID) AS (
  SELECT @StartCarID 
  UNION ALL
  SELECT ch.CarID1
  FROM car_hierachy ch
  JOIN car_left cl
  ON cl.CarID = ch.CarID2
),
car_right(CarID) AS (
  SELECT MIN(CarID) 
  FROM car_left
  UNION ALL
  SELECT ch.CarID2
  FROM car_hierachy ch
  JOIN car_right cr
  ON cr.CarID = ch.CarID1
)
SELECT *
FROM car_right ac
JOIN dbo.Car c
ON ac.CarID = c.CarID
JOIN dbo.CarPart cp
ON c.CarID = cp.CarID
ORDER BY c.CarId, cp.PartId;

SQLFiddle

. , . . . .

( chevy Toyota, , ars . Toyota, Ford.)

+2

More articles:


All Articles