JOIN using negation clauses

Question

I've got the following table in a Postgres database:

Table Example:

---------------------------
   name     |    number
---------------------------
DefaultName |       1
DefaultName |       2
DefaultName |       3
DefaultName |       4
Charlie     |       1
Charlie     |       3
Charlie     |       4
Charlie     |       5
Amanda      |       2
Amanda      |       3
Amanda      |       4
Amanda      |       5

I need to get the "number"s that are present in the 'DefaultName', but that are not present in each "name"s that differ from 'DefaultName'. In this case, it would return:

---------------------------
   names    |    numbers
---------------------------
Charlie     |       2
Amanda      |       1

I am trying a Left Join like the one below, but I can't figure a way to get the DefaultName numbers crossed with a negation with the other names'...

SELECT Test_Configs.name, Default_Configs.number

FROM Example AS Test_Configs  
    LEFT JOIN Example AS Default_Configs      
        ON Default_Configs.name = 'DefaultName'

Define "missing". The ones missing between min and max value of a name or overall or some given min / max? Or just the numbers showing up with 'DefaultName'? — Erwin Brandstetter, Sep 03 '14 at 21:14
No, it has nothing to do with the order, or max/min values. It has to do with values that are present in DefaultName, but not in the other names — ZeldaElf, Sep 03 '14 at 21:17
I changed my statements, maybe I made myself a little bit clearer now — ZeldaElf, Sep 03 '14 at 21:20

score 3 · Accepted Answer · edited May 23 '17 at 11:49

3

I would generate the whole range per name and LEFT JOIN to the base table to eliminate the present ones:

SELECT n.name, nr.number
FROM  (
   SELECT DISTINCT name
   FROM   example
   WHERE  name <> 'DefaultName'
   ) n                         -- all names except 'DefaultName'
CROSS  JOIN (
   SELECT number               -- assuming distinct numbers for 'DefaultName'
   FROM   example
   WHERE  name = 'DefaultName'
   ) nr                        -- combine with numbers from 'DefaultName'
LEFT   JOIN example x USING (name, number)
WHERE  x.number IS NULL;       -- minus existing ones

To list only the gaps for each name individually:

SELECT n.name, nr.number
FROM  (
   SELECT name, min(number) AS min_nr, max(number) AS max_nr
   FROM   example
   GROUP  BY 1
   ) n
 , generate_series(n.min_nr, n.max_nr) AS nr(number)
LEFT   JOIN example x USING (name, number)
WHERE  x.number IS NULL;

SQL Fiddle.

Here are the basic techniques to exclude rows existing in another table (a derived table in this example):

Select rows which are not present in other table

edited May 23 '17 at 11:49

Community

1
1

answered Sep 03 '14 at 21:11

Erwin Brandstetter

605,456
145
1,078
1,228

I'm not getting those values I intended in the post... I think that maybe the LEFT JOIN statement isn't working well, for the numbers are not coming... – ZeldaElf Sep 03 '14 at 21:31
1

@ZeldaElf: That's odd, because I do get the result you displayed: http://sqlfiddle.com/#!15/8aa67/1 and `LEFT JOIN` should be working very well, indeed. Are there any `NULL` values involved? Or are all columns defined `NOT NULL`. – Erwin Brandstetter Sep 03 '14 at 21:34
I'm sorry, friend. At Fiddle I saw a mistake about my insertions, pardon me. It worked! But I still can't figure how you did it with the CROSS one. I'll study a bit about it. Thanks a lot! – ZeldaElf Sep 03 '14 at 21:41

score 2 · Answer 2 · answered Sep 03 '14 at 21:21

It will take a few passes, select default, group names that aren't default, then left join and check for null values. Check the SQLFiddle Example.

select Names.name, DefaultConfigs.number
from Example DefaultConfigs
  cross join (
    select name
    from Example
    where name != 'DefaultName'
    group by name
    ) Names
  left join Example Missing on Missing.name = Names.name
    and Missing.number = DefaultConfigs.number
where DefaultConfigs.name = 'DefaultName'
  and Missing.name is null
;

It worked out too, my friend! Thanks for stopping by! – ZeldaElf Sep 03 '14 at 21:44 — ZeldaElf, Sep 03 '14 at 21:44

score 1 · Answer 3 · answered Sep 03 '14 at 21:33

Build all combinations of name and default numbers. Then remove those present.

select othernames.name, defaultnumbers.number
from (select number from example where name = 'DefaultName') defaultnumbers
cross join (select distinct name from example where name <> 'DefaultName') othernames
except
select name, number from example;

JOIN using negation clauses

3 Answers3