Filtering Navons that have lost their folder relationship in database query

Rhythmyx Technical Discussion
1

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
2

Could tacking on something like this work?

AND p.owner_id IN (SELECT dependent_id FROM PSX_OBJECTRELATIONSHIP WHERE dependent_id=p.owner_id and config_id=3)

Relationships with a config_id of 3 are folder relationships.

-n