multiple_id_values.rq

# Multiple ID values per item
#
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX pq: <http://www.wikidata.org/prop/qualifier/>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
#
select distinct ?wd ?wdLabel ?relation ?relationLabel ?stw ?stwLabel
where {
  service <https://query.wikidata.org/bigdata/namespace/wdq/sparql> {
    select ?wd ?wdLabel ?relation ?relationLabel ?stwId
    where {
      # identify items with multiple STW ID values
      {
        select distinct  ?wd (count(?stwId) as ?stwCount)
        where {
          # items to exclude (with reasons)
          values ( ?excludeList ) {
            ( wd:Q4115189 )  # Wikidata Sandbox item
          }
          # statements on property STW ID
          ?wd p:P3911 ?statement .
          filter(?wd not in (?excludeList))
          # any mapping relation
          ?statement pq:P4390 [] ;
                     ps:P3911 ?stwId .
        }
        group by ?wd 
        having (?stwCount > 1)
      }
      # expand to all existing STW IDs and relations for the item
      ?wd p:P3911 ?statement .
      # any mapping relation
      ?statement pq:P4390 ?relation ;
                 ps:P3911 ?stwId .
      filter(isLiteral(?stwId))
      #
      # get label for the wd item, if exists
      optional {
        ?wd rdfs:label ?label .
        filter(lang(?label) = 'en')
        bind(str(?label) as ?wdLabel)
      }
      ?relation rdfs:label ?relationLabelLang .
      filter(lang(?relationLabelLang) = 'en')
      bind(str(?relationLabelLang) as ?relationLabel)
    }
  }
  # look up STW descriptors and labels
  bind(uri(concat('http://zbw.eu/stw/descriptor/', ?stwId)) as ?stw)
  ?stw skos:prefLabel ?stwLabelLang .
  filter(lang(?stwLabelLang) = 'en')
  bind(str(?stwLabelLang) as ?stwLabel)
}
order by ?wdLabel desc(?relation)