StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POChallenging recursive T-SQL query
text
Body
copied!<p>Here is my scenario. Let's say I have two tables "Car" and "CarPart". The car consist of many parts and each part can belong to multiple cars. One complication in my case is that each part gets a new PartID, even if it's the same part name, but it simply belongs to a different car. This is something I have no control over, so just bear with me. Here is the script to set things up.</p> <pre><code>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) </code></pre> <p>As you can see, the part "Engine" belongs to both "Chevy" and "Ford" and is listed twice with different IDs. Once again, this is a design limitation I have to live with. </p> <p>Here is what I need to accomplis: <strong>given a car, I need to find all of the parts for this car and all of the other cars that these parts belong to. I have to continue finding parts and cars in a recursive manner until I reach the end of the chain</strong>. The logic can be outlined as follows: @StartCar --> Parts of a @StartCar --> Other parts by the same name --> get Id's of those "other" parts --> Get cars which "own" those parts --> start over and repeat until the end of the chain is reached. </p> <p>To solve my problem, I tried this query:</p> <pre><code>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 </code></pre> <p>However, it gets into an infinite loop and terminates. I would expect see the output similar to this:</p> <pre><code>CarName PartName Chevy Engine Chevy Door Ford Engine Ford Wheel Toyota Door Toyota Window Hugo Window </code></pre> <p>I appreciante any pointers.</p> <p>Thank you!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload