SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help with recursive query


Need help with recursive query

Author
Message
Misha_SQL
Misha_SQL
SSC Eights!
SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)

Group: General Forum Members
Points: 944 Visits: 1003
Hello,

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 mutliple cars. One complication in my case is that each part gets a new PartID, even if it's the same part, which 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.


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

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)



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. Here is what I need to accomplish. 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. I tried this query:


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



However, it gets into an infinite loop and terminates. I would expect see the output similar to this:

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

I appreciante any pointers.

Thank you!



mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1556 Visits: 3317
Hi

Does this do what you require?
;with originalCar AS (
SELECT CarName, PartName
FROM Car c INNER JOIN CarPart cp ON c.CarID = cp.CarID
WHERE CarName = @StartCar
),
otherCars AS (
select carName, partName
FROM Car c INNER JOIN CarPart cp ON c.CarID = cp.CarID
WHERE carName in (
SELECT carName
FROM Car c INNER JOIN CarPart cp ON c.CarID = cp.CarID
WHERE PartName in (SELECT PartName FROM originalCar)
and CarName != @startCar
)
)
select carname, partname
from originalCar
union all
select carname, partname
from otherCars
order by CarName


Misha_SQL
Misha_SQL
SSC Eights!
SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)

Group: General Forum Members
Points: 944 Visits: 1003
First of all thank you for replying! It's close, but not quite. As I said, I need to get the entire chain Car -> CarPart -> Car -> CarPart, etc. The query you wrote gets the cars and their parts, which are directly related to the @StartCar and its parts. However, it doesn't get the rest of the chain. If I were to revise my setup code to this:



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, I added "Hugo", which will not get returned because it's not related to the @StartCar "Chevy" or its parts.

I still think it has to be some sort of recursive CTE, but I can't figure out how to stop it and prevent infinite loop.



Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38849 Visits: 38508
I don't see a need for a recursive query:



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

select
c.CarName,
cp.PartName
from
dbo.Car c
inner join dbo.CarPart cp
on (c.CarID = cp.CarID)
order by
c.CarName;

IF OBJECT_ID('Car') IS NOT NULL DROP TABLE Car;
IF OBJECT_ID('CarPart') IS NOT NULL DROP TABLE CarPart;




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Misha_SQL
Misha_SQL
SSC Eights!
SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)

Group: General Forum Members
Points: 944 Visits: 1003
Lynn,

Thank you for your reply! The query you listed will return all of the cars and their parts, but this is not what I am after. My goal is as follows: given the initial "StartCar" find all of its parts, next find all of the cars which have the same parts (note in my original post that the parts get new numbers when they belong to different cars, so I have to detect the fact that the part is "shared" based on the PartName), repeat the process finding cars and their parts until I reach the end of the chain.

In my example above, I would expect "Honda" and "Nissan" NOT to be returned since they don't share any parts with cars, which share parts with my @StartCar of "Chevy".

Thank you!



Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38849 Visits: 38508
mishaluba (3/22/2013)
Lynn,

Thank you for your reply! The query you listed will return all of the cars and their parts, but this is not what I am after. My goal is as follows: given the initial "StartCar" find all of its parts, next find all of the cars which have the same parts (note in my original post that the parts get new numbers when they belong to different cars, so I have to detect the fact that the part is "shared" based on the PartName), repeat the process finding cars and their parts until I reach the end of the chain.

In my example above, I would expect "Honda" and "Nissan" NOT to be returned since they don't share any parts with cars, which share parts with my @StartCar of "Chevy".

Thank you!


I'm not sure what you mean by share parts. The only thing they share in your sample data is the same name (Engine, for example). None of the parts listed have the same part number.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Misha_SQL
Misha_SQL
SSC Eights!
SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)

Group: General Forum Members
Points: 944 Visits: 1003
Yes, that is correct. By "share parts" I mean that they share a part by the same name. That is why my "attempted" recursive query does a JOIN on PartName rather than PartID. Unfortunately this is a limitation of the design I have to live with. Also note that this is just an example to illustrate the problem (the real database is not about cars or parts). So once again, what I need to do is 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.

Does this help?



Erin Ramsay
Erin Ramsay
SSC Eights!
SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)

Group: General Forum Members
Points: 815 Visits: 1099
I would expect, considering the number of parts in a car and the number of shared parts across cars that your recursive query is probably going to return all cars and all car parts when fully explored anyway.
Misha_SQL
Misha_SQL
SSC Eights!
SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)

Group: General Forum Members
Points: 944 Visits: 1003
Thank you, but as I said, the Cars and CarParts was used by me simply to illustrate the problem. The actual database has nothing to do with either and the query will only return a fraction of avaialbe data.



Misha_SQL
Misha_SQL
SSC Eights!
SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)

Group: General Forum Members
Points: 944 Visits: 1003
Here is the solution to this problem, which works correctly (credit to Steve Kass: http://stackoverflow.com/users/139164/steve-kass):


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





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search