January 4, 2009 at 3:21 pm
I have a table on MS SQL 2005 with a customer_id and a link_id. The things table has link_id's, and a next_link_id which is a foreign key back into the things table.
CREATE TABLE customer (
customer_id as int,
link_id as int
CONSTRAINT customer_pk PRIMARY KEY (customer_id)
);
CREATE TABLE things (
link_id as int,
next_link_id as int
CONSTRAINT next_link_id_fk FOREIGN KEY REFERENCES things (link_id)
CONSTRAINT things_pk PRIMARY KEY (link_id)
);
I'd like to create a view (or a function that returns a view, if they look just like views in Access) where I can allow someone to retrieve all the things for a customer. But so far I can only do it at the first level ...
SELECT customer_id,link_id FROM customer c LEFT OUTER JOIN things t ON c.link_id = t.link_id
I can't work out how to do that recursively with checking down the next_link_id chain. I investigated CTEs but I couldn't work out how to make them do what I want. And I don't know T-SQL.
Can anyone help?
January 4, 2009 at 7:01 pm
Resolved:
WITH Link_CTE (link_id, next_link_id, root, level) AS
(
SELECT link_id, next_link_id, root = link_id, 1 AS level
FROM things t
WHERE EXISTS (SELECT * FROM customer c WHERE c.link_id = t.link_id)
UNION ALL
SELECT t.link_id, t.next_link_id, t.root, level + 1 as level
FROM things t
INNER JOIN Link_CTE l
ON t.link_id = l.next_link_id
AND t.link_id > l.next_link_id
AND level <= 100
)
SELECT c.customer_id, l.link_id FROM
Link_CTE l
RIGHT OUTER JOIN customer c
ON c.link_id = l.root
ORDER BY c.customer_id, l.link_id, l.root
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy