PostgreSQL : comparing two sets of results does not work

Question

I have a table that contains 3 columns of ids, clothes, shoes, customers and relates them.

I have a query that works fine :

select clothes, shoes from table where customers = 101 (all clothes and shoes of customer 101). This returns

clothes - shoes (SET A)
1          6
1          2
33         12
24         null

Another query that works fine :

select clothes ,shoes from table where customers in (select customers from table where clothes = 1 and customers <> 101 ) (all clothes and shoes of any other customer than 101, with specified clothes). This returns

shoes - clothes(SET B)
6          null
null         24
1            1
2            1 
12          null
null         26
14           null

Now I want to get all clothes and shoes from SET A that are not in SET B.

So (example) select from SET A where NOT IN SET B. This should return just clothes 33, right?

I try to convert this to a working query :

select clothes, shoes from table where  customers = 101 
and
(clothes,shoes) not in 
 (   
   select clothes,shoes from
   table where customers in 
   (select  customers   from table where clothes = 1 and customers <> 101 ) 
 ) ;

I tried different syntaxes, but the above looks more logic.

Problem is I never get clothes 33, just an empty set.

How do I fix this? What goes wrong?

Thanks

Edit , here is the contents of the table

id  shoes   customers   clothes
1    1      1           1
2    1      4           1
3    1      5           1
4    2      2           2
5    2      3           1
6    1      3           1
44   2      101         1
46   6      101         1
49   12     101         33
51   13     102 
52          101         24
59          107         51
60          107         24
62   23     108         51
63   23     108         2
93          124         25
95   6      125 
98          127         25
100  3      128 
103  24     131 
104  25     132 
105         102         28
106  10     102 
107  23     133 
108         4           26
109  6      4   
110         4           24
111  12     4   
112  14     4   
116         102         48
117         102         24
118         102         25
119         102         26
120         102         29
122         134         31

@Hambone What do you need specifically ? What part is unclear? — codebot, Nov 17 '18 at 19:26
Just what the actual contents of `table`. I can guess what is there for customer 101 (the three records from the first query), but not for what else is in the table. — Hambone, Nov 17 '18 at 19:29
Okay, my bad... I see why you were confused as to why I wanted to see the entire dataset. You're right. Sorry for the extra work — Hambone, Nov 17 '18 at 20:29
Your result for set A doesn't even fit your supposed table content: 44 2 101 1 46 6 101 1 49 12 101 33 52 101 24 it clearly has 4 rows, not 3, i suggest to make table backup and post sql script here — Vancalar, Nov 17 '18 at 22:54
"Now I want to get all clothes and shoes from SET A that are not in SET B. So (example) select from SET A where NOT IN SET B. This should return just clothes 33, right? " All pairs from set A ARE NOT in set B... So either You want "to get all clothes and shoes from SET A..." or "to get all clothes OR shoes from SET A..." It's kind of unclear of what You trying to achieve here. — Vancalar, Nov 18 '18 at 00:30
@Vancalar I posted SET A and SET B as examples, before posting the whole table content, so they are a bit different. I apologize, I updated the SETs now. Also, I want to check everything from SET A against SET B and if it exists in SET B , do not keep it. Clothes 24 of SET A is also on SET B, so dont keep it. Clothes 33 is in SET A but not in SET B so keep it. All shoes in SET A,are also in SET B, so dont keep anything from shoes. Thanks — codebot, Nov 18 '18 at 00:43

score 1 · Answer 1 · answered Nov 17 '18 at 20:29

1

The except clause in PostgreSQL works the way the minus operator does in Oracle. I think that will give you what you want.

I think notionally your query looks right, but I suspect those pesky nulls are impacting your results. Just like a null is not-NOT equal to 5 (it's nothing, therefore it's neither equal to nor not equal to anything), a null is also not-NOT "in" anything...

select clothes, shoes
from table1
where customers = 101

except  

select clothes, shoes
from table1
where customers in (
  select customers
  from table1
  where clothes = 1 and customers != 101
)

answered Nov 17 '18 at 20:29

Hambone

15,600
8
46
69

No, I am sorry for posting the table like that. I didnt know how else I could post it. Any online tool that I can upload it btw? Anyway,bad news : nice thinking, but the result is wrong. I get back `clothes 1,33` and `shoes 6,12`, but the right answer is just `clothes 33` . I dont think that `null` is the problem here. I dont know how to proceed. And yes, the query looks logic, this is killing me. – codebot Nov 17 '18 at 22:06
I also tried the following syntax ` with otherdata as (select clothes ,shoes from table where customers in ( select customers from table where clothes= 1 and customers != 101 ) ) select clothes, shoes from table where customers = 101 and clothes not in otherdata.clothes and shoes not in otherdata .shoes;` But I get `ERROR: syntax error at or near "otherdata " LINE 36: ...where customers= 101 and clothes not in otherdata ...` Thanks – codebot Nov 17 '18 at 22:22

Vancalar · Answer 2 · 2018-11-18T13:04:16.323

For PostgreSQL null is undefined value, so You must get rid of potential nulls in your result:

select id,clothes,shoes from t1 where  customers = 101 -- or select id... 
and (
 clothes  not in 
 (   
   select COALESCE(clothes,-1) from
   t1 where customers in 
   (select  customers   from t1 where clothes = 1 and customers <> 101 ) 
 ) 
OR 
 shoes not in 
 (   
   select COALESCE(shoes,-1) from
   t1 where customers in 
   (select  customers   from t1 where clothes = 1 and customers <> 101 ) 
  )
 )

if You wanted unique pairs you would use:

select clothes, shoes from t1 where  customers = 101 
and
(clothes,shoes)  not in 
 (   
   select coalesce(clothes,-1),coalesce(shoes,-1) from
   t1 where customers in 
   (select  customers   from t1 where clothes = 1 and customers <> 101 ) 
 ) ;

You can't get "clothes 33" if You are selecting both clothes and shoes columns...

Also if u need to know exactly which column, clothes or shoes was unique to this customer, You might use this little "hack":

select id,clothes,-1 AS shoes from t1 where  customers = 101 
and 
 clothes  not in 
 (   
   select COALESCE(clothes,-1) from
   t1 where customers in 
   (select  customers   from t1 where clothes = 1 and customers <> 101) 
 )  
 UNION
  select id,-1,shoes from t1 where  customers = 101 
   and 
   shoes not in 
   (   
    select COALESCE(shoes,-1) from
    t1 where customers in 
    (select  customers   from t1 where clothes = 1 and customers <> 101) 
   )

And Your result would be:

id=49, clothes=33, shoes=-1

(I assume that there aren't any clothes or shoes with id -1, You may put any exotic value here)

Cheers

PostgreSQL : comparing two sets of results does not work

2 Answers2