CODEWITHSUNDEEP
sqloracle-database
Phate
Phate

Reputation: 6612

SQL minus with a subset of columns: possible?

I have 2 tables, T1 and T2.

T1 is

C1 | GROUP | SECTOR

while T2 is

CODE | C1 | GROUP | SECTOR

I have to select rows from T2 belonging to a specific group and having code = 100. Those rows normally have to be processed, in particular have to be inserted in a table T3:

C1 | GROUP | SECTOR

However, if there is at least one sector, in T1 with 

C1 > 0

, belonging to the same GROUP, this T2 row has to be excluded.

So, my selection query on T2 would be :

select C1,C2,GROUP,SECTOR from T2 where CODE=100 group by(C1,C2,GROUP,SECTOR);
 --adding C1,C2 and SECTOR to group by because I need their values...

And the one on T1:

select C1,C2,GROUP from T1 group by (C1,C2,GROUP) having C1>0

Now I would have to do:

first_selection minus second_selection

But, as you can see, first selection has SECTOR column while second selection has not, so I don't know how to exclude it!

Example: T1:

0|A|A1
2|A|A2
0|B|B1
0|B|B2

T2:

100|A|A1
100|A|A2
100|B|B1

First selection on T2 gives me "A","B". Anyway in T1 you have that the sector A2 contains C1>0 (2), so A has to be excluded. "B" is fine, as every row in T1 has C1=0.

Upvotes: 1

Views: 933

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

you may code it nearly literally 

select * from T2 where code = 100
and not exists (select NULL from T1 where 
                T1.group = T2.group and
                T1.c1 > 0)

i.e. you need all records with T2.code = 100 where NOT EXISTS a records in T1 with the same group (T1.group = T2.group) and T1.c1 > 0. The select NULL in the subquery may look strange and you may write instead of null e.g. 'found something' The point is that NOT EXISTS checks only if the subquery returns some records - the value is uninteresting.

Upvotes: 2

Related Questions