|
|
Posted by John Bell on 10/01/05 12:09
Hi Thomas
This gives you the results you wanted, but if you have more than one chain
you will need a means of identifying them.
CREATE VIEW AllIds AS
SELECT My_Id FROM Chain
UNION SELECT My_Id FROM Copy_To
UNION SELECT My_Id FROM Copy_From
SELECT I.My_Id,
CASE WHEN I.My_Id = C.My_Id THEN C.Orig_ID ELSE C.My_Id END AS Orig_ID,
CASE WHEN I.My_Id = C.Copy_to THEN E.Copy_to ELSE (SELECT MIN(My_Id) FROM
AllIds P WHERE I.My_Id < P.My_Id) END AS Copy_To,
CASE WHEN I.My_Id = C.My_Id THEN C.Copy_From ELSE (SELECT MAX(My_Id) FROM
AllIds P WHERE I.My_Id > P.My_Id) END AS Copy_from
FROM AllIds I
JOIN Chain C ON C.My_Id <= I.My_Id AND C.Copy_to >= I.My_Id
JOIN Chain E ON C.Copy_to = E.My_Id AND E.Orig_id = C.My_id
ORDER BY I.My_Id
John
"Thomas R. Hummel" <tom_hummel@hotmail.com> wrote in message
news:1128138055.333646.242360@g14g2000cwa.googlegroups.com...
> Hi John,
>
> I think that I may have been unclear about the problem. The Copy_To and
> Copy_From columns are IDs. I should have probably put foreign keys to
> My_ID in my example code to make that clear. Also, the Copy_To and
> Copy_From tables are direct imports of files that I receive, so I have
> no control over how that information is presented. If you had a way
> that I could convert those rows into something like what you have
> (instead of Claim and Part I would basically have the Orig_ID and
> My_ID) then that would probably solve my problem.
>
> Thanks,
> -Tom.
>
Navigation:
[Reply to this message]
|