RDF SPARQL Query - Find tuples that are not part of both conditions (LEFT JOIN in SQL)

Question

Below is the data set I have:

:project#1   :hasRevision        :revision#1
:revision#1  :hasRevisionNumber  1 
:project#1   :hasRevision        :revision#2
:revision#2  :hasRevisionNumber  2
:project#1   :hasRevision        :revision#3
:revision#3  :hasRevisionNumber  3
:revision#1  :committed          :A1
:A1          :hasId              1
:revision#2  :committed          :A2
:A2          :hasId              2
:revision#3  :reverted           :A1

Use case:

Need to fetch attributes committed in each revision.
- If the user asks for :revision#1, A1 should be returned.
- If the user asks for :revision#2, A1 and A2 should be returned.
- If the user asks for :revision#3, only A2 should be returned as A1 is :reverted in :revision#3.

The closest query I could come up with is below which is not working:

select ?attribute ?id WHERE { 
    :project1  :hasRevision       ?revision . 
    ?revision  :hasRevisionNumber ?revNum ; 
               :committed         ?attribute . 
   ?attribute  :hasId             ?id . 
   FILTER NOT EXISTS { ?revision :reverted ?attribute } 
   FILTER ( ( ?revNum <= 3 && ?revNum > 0 ) && ?id in (1,2) ) 
}

Actual Output:

A1 & A2

Expected Output:

A2

I understand the issue. Not able to come up with a proper query. Can any of you please help.

Thanks in advance.

You're doing `FILTER NOT EXISTS { ?revision :deleted ?attribute} `, but why do you use `:deleted` instead of `:reverted` ? The same for `:created` instead of `:committed` - right now, the query doesn't match the data — UninformedUser, Sep 21 '18 at 08:38
In addition, `?revNum <= "3" && ?revNum > "0"` might lead to problems given that you compare strings here instead of numbers — UninformedUser, Sep 21 '18 at 08:41
that was just a typo. I tweaked the query as I couldn't put the exact scenario as in my scenario.Please check now. — Linz, Sep 21 '18 at 09:07
1. your data couldn't be parsed with the `#` in a prefixed name, thus I changed to `@prefix : . :project1 :hasRevision :revison1 . :revision1 :hasRevisionNumber 1 . :project1 :hasRevision :revision2 . :revision2 :hasRevisionNumber 2 . :project1 :hasRevision :revision3 . :revision3 :hasRevisionNumber 3 . :revision1 :committed :A1 . :A1 :hasId 1 . :revision2 :committed :A2 . :A2 :hasId 2 . :revision3 :reverted :A1 . ` — UninformedUser, Sep 21 '18 at 12:38
2. your query works as expected on the data using Jena CLI tool. — UninformedUser, Sep 21 '18 at 12:39
While you inserted the data, "revision1" was spelled wrong. That's why! Can you please check again. Thank you for your time. — Linz, Sep 21 '18 at 14:55
Well, it was again you who made the typo (look at the edit history before TallTed fixed everything) ... I just replaced all `#` chars. And yes, now it returns `A1` and `A2` — UninformedUser, Sep 21 '18 at 15:47
There is a join with ?revision within FILTER NOT EXISIT but You should use a different valiable there. E.g. when :revision1 is bound to ?revision the filer do not find a statement (:revison1 :reversed ?attribute) so the solution where ?attribute=:A1 is added to teh result set — Damyan Ognyanov, Sep 21 '18 at 16:29
@DamyanOgnyanov In that case, A1 will not be returned even in revision 1 or 2 as there is a "reverted" relationship exists from revision 3 — Linz, Sep 24 '18 at 02:22
@Linz you could filter out other revisions on additional criteria within NOT EXISIT, e.g. for instance, its revision number being less than or equal to the target — Damyan Ognyanov, Sep 25 '18 at 06:13

Damyan Ognyanov · Accepted Answer · 2018-09-25T06:17:20.107

Make use of different variable in the FILTER NOT EXISIT, e.g.

FILTER NOT EXISTS { ?otherRevision :reverted ?attribute }

Edit: after the additional comment from @Linz and adding a filter to look only for revisions with smaller revison numbers.

prefix : <http://base.org/>
select ?attribute ?id WHERE { 
    bind (3 as ?targetRevisionNum )
    :project1  :hasRevision       ?revision . 
    ?revision  :hasRevisionNumber ?revNum ; 
               :committed         ?attribute . 
   ?attribute  :hasId             ?id . 
    FILTER NOT EXISTS { 
        ?other :reverted ?attribute . 
        ?other :hasRevisionNumber ?otherRevNum .
        filter (?otherRevNum <= ?targetRevisionNum )
    } 
   FILTER ( ( ?revNum <= ?targetRevisionNum && ?revNum > 0 ) && ?id in (1,2) ) 
}

In that case , A1 will not be returned even in revision 1 or 2 as there is a "reverted" relationship exists from revision 3. — Linz, Sep 22 '18 at 11:02

score 0 · Answer 2 · answered Sep 25 '18 at 06:40

Found a workaround.
An object can be :committed and :reverted only once. So used having with aggregate function to filter based on the count of relationships from "revision" to "object" o my final query is below:

prefix : <http://test.org/> 

select  ?attribute WHERE { 

        :project1  :hasRevision       ?revision . 
        ?revision  :hasRevisionNumber ?revNum ; 
                   ?s   ?attribute .
        ?attribute :hasId ?id;
        FILTER ( ( ?revNum <= 3 && ?revNum > 0 ) && ?id in (1,2) )  

}  
group by ?attribute 
having (count(?attribute) < 2 )

Output:

attribute
<http://test.org/A2>

I would be glad if anyone could find me a better query. Thank you everyone!

RDF SPARQL Query - Find tuples that are not part of both conditions (LEFT JOIN in SQL)

Use case:

Actual Output:

Expected Output:

2 Answers2