Get all tie-in records that DON'T exist (opposite/inverse of inner join)

alex.pritchard · April 4, 2019, 4:50pm

gist.github.com

https://gist.github.com/PritchardAlexander/0cb4c6555827b835b19113afdebfbcea

gistfile1.txt

Query to get tie-in records that DO exist (essentially, all manufacturer defects that exist):

  select
      md.f_manufacturerId, md.f_defectId
  from productionlines as md
  inner join productionlines as m on m.type="manufacturer" and m.manufacturerId=md.f_manufacturerId
  inner join productionlines as d on d.type="defect" and d.defectId=md.f_defectId
  where md.type="manufacturerdefect"
  order by md.f_manufacturerId

This file has been truncated. show original

I feel like the gist describes this pretty well. Although even now, the inner join query takes 12 seconds to run (perhaps we need better indexing?). I’m not super excited about that part and would love to make things faster…

Thoughts?

alex.pritchard · April 4, 2019, 5:02pm

Okay, so to get all combinations (currently have many of these), I can run this query:

select
    m.manufacturerId as manufacturerId, d.defectId as defectId
from productionlines as d
inner join productionlines as m on m.type="manufacturer"
where d.type="defect"
order by m.manufacturerId

Now, if I could filter out of this list existing manufacturer defects… then return what remains…

alex.pritchard · April 4, 2019, 5:23pm

STEP 1: Find all combinations.

STEP 2: Exclude all existing manufacturing defects.

(select
    m.manufacturerId as manufacturerId, d.defectId as defectId
from productionlines as d
inner join productionlines as m on m.type="manufacturer"
where d.type="defect"
order by m.manufacturerId)
EXCEPT
(select
    md.f_manufacturerId as manufacturerId, md.f_defectId as defectId
from productionlines as md
where md.type="manufacturerdefect")
order by manufacturerId