CODEWITHSUNDEEP
oracle-databasegroup-by
MikeTWebb
MikeTWebb

Reputation: 9279

Oracle Count across two tables

I have two tables that have identical columns. One table houses imported data, the other table houses data specific to my application:

IMPORT_TABLE     MY_TABLE
COL1   COL2      COL1   COL2
"A"    "1"       "A"    "2"
"B"    "1"       "B"    "1"

What I need to do is write a single query that will tell me, for a given value in COL1, I have differing values in COL2 across the tables. So, when I run the query I woud get back the value "A" for COL1. This tells me that I need to insert "A" "1" into MY_TABLE.

How can I accomplish the query? I know how to do a Group By on a single table but not across tables.

Upvotes: 0

Views: 339

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

If you just want to get the rows in IMPORT_TABLE that don't exist in MY_TABLE

SELECT col1, col2
  FROM import_table
MINUS
SELECT col1, col2
  FROM my_table

If col1 is unique, you could also do

SELECT import.col1, import.col2 imported_col2, mytbl.col2 my_col2
  FROM import_table import 
       FULL OUTER JOIN my_table mytbl ON (mytbl.col1 = import.col1)

Upvotes: 2

Related Questions