widsnet.com
1Jul/110

Collection finder

A report to find the parents to a collection to be able to find the collection if you only have the name or ID.

SELECT
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT5ID.parentCollectionID) AS 'Parent 5',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT4ID.parentCollectionID) AS 'Parent 4',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT3ID.parentCollectionID) AS 'Parent 3',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT2ID.parentCollectionID) AS 'Parent 2',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT1ID.parentCollectionID) AS 'Parent 1',
COLL.Name AS 'Collection Name',
COLL.CollectionID AS 'CollectionID'

FROM v_Collection COLL
LEFT JOIN v_CollectToSubCollect COLLISPARENT ON COLL.CollectionID = COLLISPARENT.parentCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT1ID ON COLL.CollectionID = COLLPARENT1ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT2ID ON COLLPARENT1ID.parentCollectionID = COLLPARENT2ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT3ID ON COLLPARENT2ID.parentCollectionID = COLLPARENT3ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT4ID ON COLLPARENT3ID.parentCollectionID = COLLPARENT4ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT5ID ON COLLPARENT4ID.parentCollectionID = COLLPARENT5ID.subCollectionID

WHERE CollectionID=@CollName

Prompt:

begin
   if (@__filterwildcard = '')
      SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
   else
      SELECT DISTINCT CollectionID, Name FROM v_Collection
      WHERE Name like @__filterwildcard
      ORDER BY Name
end
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.