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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply