I am writing a SQL query to get a list of navons and their current landing pages but I am getting duplicates where a landing page has moved from one navon to another. The duplicates are often the latest revision of that navon but the duplicate navon no longer has a folder relationship.
How do I check to see if a navon has a folder relationship? (The folder_id in PSX_OBJECTRELATIONSHIP seems to always be null when the slot is the navon landing page slot) Is there another table that tracks the navon to folder relationship or is it a different slot id in PSX_OBJECTRELATIONSHIP?
I would like to filter the following query by removing navons that don’t have a folder relationship.
SELECT p.dependent_id AS landing_page_id , p.owner_id as navon_id , p.owner_revision -- navon revision , s.displaytitle FROM psx_objectrelationship p JOIN contentstatus c ON c.contentid = p.owner_id JOIN rxs_ct_shared s ON s.contentid = p.owner_id WHERE c.communityid = 1009 AND p.slot_id = 510 -- landing page AND p.owner_revision = c.public_revision