How to distinguish different nodes of type "content".

[Muffex · Accepted solution]

Well, I finally figured "something" out.

I can retrieve most of the nodes that refer to "files". It's neither well optimized nor can I guarantee it will work.

But for my needs it is currently sufficient.

SELECT n.id AS nodeId, n.uuid AS fileId, n.audit_created AS created, n.audit_modified AS last_updated, anp.string_value AS name, n.audit_creator AS creator, CONCAT('http://alfresco.bas.uni-koblenz.de/share/page/document-details?nodeRef=',store.protocol,'://',store.identifier,'/',n.uuid) AS content
  FROM alf_node AS n
    JOIN alf_qname AS q on (n.type_qname_id = q.id AND q.local_name='content')
    JOIN alf_store AS store ON (store.id = n.store_id)
    JOIN alf_node_properties AS anp on (n.id = anp.node_id)
    LEFT JOIN alf_content_data AS acd ON (anp.long_value = acd.id)
    JOIN alf_child_assoc AS aca ON (aca.child_node_id = n.id)
    JOIN alf_node AS nP ON (aca.parent_node_id = nP.id)
    JOIN alf_qname AS aqn ON (np.type_qname_id = aqn.id)
    WHERE anp.qname_id IN 
      (SELECT id FROM alf_qname
        WHERE local_name='name' 
          AND ns_id IN 
            (SELECT ns2.id FROM alf_namespace AS ns2 WHERE uri LIKE '%content%'))
      AND n.audit_creator NOT LIKE 'System'
      AND aqn.local_name not like 'archiveUser'
      AND aqn.local_name not like 'person'
      AND anp.string_value like '%.%'
      AND anp.string_value NOT LIKE '%~%~%'
      AND anp.string_value NOT LIKE 'dashboard.xml'