CODEWITHSUNDEEP
sqlsqlitetuplesmembership
theRook
theRook

Reputation: 43

Check for tuple non membership in sqlite

I am working on an sql assignment to determine the "degree of separation" between various nodes on a directed graphs by iteratively adding to an existing table of paths, paths that are one "link" longer. (a link is a given path of length one)

As a step in this process, I am creating a table to temporarily contain the new paths to add in a given iteration which is simply the paths added in the previous iteration extended by one link.

My issue is preventing the addition of new paths which connect two nodes that are already connected in the paths table. A path here is defined as (src, dst, length). The tables are defined with those fields as well.

This is my partial solution:

INSERT INTO pathsNew
    select p.src, l.dst, 1 + length
    from pathsLastUpdated as p, links as l
    where p.dst=l.src and ...;

I've tried augmenting the where clause with the following:

... (p.src, l.dst) not in (select src, dst from paths)

but sqlite seems not to support tuples. What is the simplest work around?

Upvotes: 0

Views: 170

Answers (1)

Tom H
Tom H

Reputation: 47392

You can use:

WHERE
    NOT EXISTS (SELECT * FROM paths P2 WHERE P2.src = P.src AND P2.dst = P.dst

You'll run into another potential issue with that approach though - namely paths that loop continually. If you're just looking for shortest path and you're already guaranteed to have a path then that might be ok (maybe just some extra processing), but if the path might not exists then you could end up with an infinite loop.

Upvotes: 1

Related Questions