2

I have a Lookup table like below

Table MASTER

CD SEQ DEF
1 1 A
1 1 B
1 1 C
2 1 A
2 1 B
2 1 C
2 1 D

Table INFO

SID CD SEQ DEF
1000 2 1 A
1000 2 1 B
1000 2 1 C

2000 2 1 B
2000 2 1 C
2000 2 1 D
2000 2 1 E

How should i write a query to say for the SID 1000 using the same CD, and SEQ D is missing compared to Master table. and for the SID 2000 using the same CD,SEQ All master DEF records exists and also extra DEF record E exists which is not there in Master table.

Your help is highly appreciated. Thanks

  • from master full join info where master.def is null or info.def is null – Akina Oct 4 '18 at 12:14
  • Thanks for the reponse. If making full join on cd and seq it will say all ABCDE is present on the whole but dont say it is missing in SID 1000 and few missing in SID 2000. Plz help – i44 Oct 4 '18 at 13:13
0

Assuming that the combinations of values in CD, SEQ, DEF in the MASTER table are unique, you could write a cross join which gives you all "valid" combinations for the INFO table - see Query1. Then you could this query, along with a query that returns all rows from INFO, and the set operators MINUS and UNION ALL to find the rows you are looking for (see Query2).

Query1

select sid, cd, seq, def
from 
  ( select unique sid from info )
, ( select cd, seq, def from master )
;

SID CD  SEQ DEF
1000    1   1   A    
1000    1   1   B    
1000    1   1   C    
1000    2   1   A
1000    2   1   B
1000    2   1   C
1000    2   1   D
2000    1   1   A    
2000    1   1   B    
2000    1   1   C    
2000    2   1   A
2000    2   1   B
2000    2   1   C
2000    2   1   D

Query2

select *
from (
  (
    select sid, cd, seq, def, 'missing from info' as comments     -- Query1
    from 
      ( select unique sid from info )
    , ( select cd, seq, def from master )
    minus
    select sid, cd, seq, def, 'missing from info' from info       -- INFO + comment 
  ) union all (
    select sid, cd, seq, def, 'in info (not in master)' from info -- INFO + comment
    minus
    select sid, cd, seq, def, 'in info (not in master)'           -- Query1
    from 
      ( select unique sid from info )
    , ( select cd, seq, def from master )
  )
) where cd = 2 and seq = 1 
;

Result

SID   CD  SEQ DEF  COMMENTS
1000  2   1   D    missing from info
2000  2   1   A    missing from info
2000  2   1   E    in info (not in master)

Caveat: In your question, you are saying (among other things) that "SID 2000 using the same CD,SEQ All master DEF records exist" although the combination (cd, seq, def) 2-1-A is missing from info. Query2 will report this combination to be missing.

Dbfiddle here. Table comparison technique described here.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.