JOINing on several fields, filtering only the truly different rows

Question

I have two tables, each one has 4 fields. A crc, a title, a parent and a parent_type.

The data in those tables represents files and to which entity they belong to.

A file with the same CRC can belong to multiple entities.

The first table represents a snapshot at one point in time, while the second table represents a snapshot at another point in time.

When a file is "moved" (think of it as a file system mv operation), the parent and/or the parent_type will change.

I want to get a list of the files that were moved between snapshot A and snapshot B.

This is what I currently have: http://sqlfiddle.com/#!5/89f0d4/1

Note that file 1 belongs to 2 different parents. file 4 was added/created. file 2 was moved from 2 GRUP to 3 GRUP. That is the one that I'm interested in.

Does this answer your question? https://stackoverflow.com/questions/5676559/how-to-write-not-in-sql-query-using-join — Nosajimiki, Oct 13 '17 at 20:16
@Nosajimiki No. I already know how to join (in fact, the link which I provided with my current progress already does that). The difficulty in what I'm asking for is the "multiple join with distinct". — alexandernst, Oct 13 '17 at 20:18
According to your data, a file can be in two places at the same time. Is that correct? — Gordon Linoff, Oct 13 '17 at 20:23
@GordonLinoff Yes. I already mentioned that in the description of the question :) — alexandernst, Oct 13 '17 at 20:24
Fiddles are great but what would the desired result look like — Strawberry, Oct 13 '17 at 22:56

score 0 · Answer 1 · answered Oct 13 '17 at 20:40

0

If I understand your question correctly what you need is to add a left join in the mix to exclude those who haven't changed...see below:

select a.*
  from memarxiu a
  join arxiu b
    on b.crc = a.crc
  left join (select *
               from arxiu) c
     on c.crc = a.crc
    and c.parent = a.parent
    and c.parent_type = a.parent_type
  where a.title = b.title
    and (a.parent <> b.parent
     or a.parent_type <> b.parent_type)
    and c.crc is null;

answered Oct 13 '17 at 20:40

JuveLeo1906

161
5

Let me check with my real data. – alexandernst Oct 13 '17 at 20:59
It doesn't seem to be working. Note the change in the `parent` on the element that the arrow is pointing to: https://imagebin.ca/v/3dgU4CXIylpu – alexandernst Oct 13 '17 at 21:29

JOINing on several fields, filtering only the truly different rows

1 Answers1