0

have a data structure that is value/tag inside a table on Oracle sql Lets say I have this structore

exTable

ID TAG VALUE
1 1 x
1 4 y
2 2 x
2 3 y
2 4 z

I want to retrieve the missing tags for a list of values I have, for example [1,2,3,4,5]

In this case Im searching for this as an output if I look for (1,2)

ID MISSINGTAG
1 2
1 3
1 5
2 1
2 5

Been trying with temp tables and joins but im still missing something because when i try to join I either dont get anything or just get the ones that do exists, I achieved want I want for one id using a minus

select column_value as COL_NAME from table(sys.dbms_debug_vc2coll('1', '2', '3', '4', '5')) MINUS SELECT TAG FROM exTable WHERE ID='1';

But for doing it for a list of IDS im have not had luck

If im duplicating the question let me know

0

One way is the create the cartesian product between your tags and existing id:s. From there you can subtract your existing table either by MINUS as in your example, or by EXISTS:

with d (tag, id) as (
    select tag, id 
    from table(sys.dbms_debug_vc2coll('1', '2', '3', '4', '5')) t 
    cross join ( 
        select distinct id from extable 
    )
) 
select * from d
where not exists (
    select 1 from extable x where x.id = d.id and x.tag = d.tag
);
1
  • This did the trick, thank you so much, I was missing the cross join creating the temp table Dec 11 '20 at 20:39
0

I don't fully follow your examples, if you could clarify them a little it might help, but in general it sounds like you're trying to find what data exists in one table but not another? If so, then you want to use a LEFT JOIN and a WHERE clause that filters on the missing records like so:

SELECT *
FROM TableWithAllRecords
LEFT JOIN TableMissingRecords
    ON TableWithAllRecords.KeyField = TableMissingRecords.ForeignKeyField
WHERE TableMissingRecords.ForeignKeyField IS NULL
2
  • Im working with just 1 table exTable, and using another temp one with the list of tags[1,2,3,4,5], this tagsdo exists on the exTable but for some ids, I want to find for each id what tag they lack, in this example, 1 doesnt have the 2,3 and5 tag and 2 doesnt have the 1 and 5 tag, did that help? Dec 11 '20 at 19:59
  • 1
    Whenever I see those sorts of questions I always wonder whether this is just an hypothetical example (or an exam question) or there is some business case behind it. If the latter, what is it ? It is always better to explain the real-life business context where this question needs solving. Like: what does the table describe ? What do the tags actually mean ? Dec 13 '20 at 12:07

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.