sql query

[levywasike]

hi,

i have 2 tables t1 and t2 storing different aspects of the same data. i need to create a view v1 that select rows from both tables t1 and t2 using this simple rule:

- if a row appears in both t1 and t2, then select the row in t1, else select row in t2

pls help. thanx

[PAVB]

Is it homework, isn't it?
Wouldn't be nice if you show us your best effort?

[levywasike]

Is it homework, isn't it?
Wouldn't be nice if you show us your best effort?

actually i have been doing some development for an election application for a media house. the database stores the official results and the provisional results gathered by the media. for the reporting in this application, the requirement is that whenever official results are available, then these are used otherwise provisional results are used.

the challenge is i need to create this logic from the database and not from the application. this is because many other users are connecting and reading the results directly from the views for publishing purposes.

right now i have a view that gets all rows (using union), where i have each row labelled as OFFICIAL or PROVISIONAL respectively.

i need to get rid of this and display results as per the rules without labelling.

[levywasike]

Is it homework, isn't it?
Wouldn't be nice if you show us your best effort?

actually i have been doing some development for an election application for a media house. the database stores the official results and the provisional results gathered by the media. for the reporting in this application, the requirement is that whenever official results are available, then these are used otherwise provisional results are used.

the challenge is i need to create this logic from the database and not from the application. this is because many other users are connecting and reading the results directly from the views for publishing purposes.

right now i have a view that gets all rows (using union), where i have each row labelled as OFFICIAL or PROVISIONAL respectively.

i need to get rid of this and display results as per the rules without labelling.

[PAVB]

Okay... follows a workable pseudo-code

(
select from t1
union
select from t2
)
minus
(
select from t2 where exist on t1
)

The key of it is the use of MINUS to take out the rows you don't want.

[ebrian]

Without knowing what your data really looks like, here's an example.

SQL> select * from tab_a;

NAME                              RESULTS SOURCE
------------------------------ ---------- --------------------
player_1                               80 OFFICIAL
player_3                               30 OFFICIAL

SQL> select * from tab_b;

NAME                              RESULTS SOURCE
------------------------------ ---------- --------------------
player_1                              110 PROVISIONAL
player_3                               30 PROVISIONAL
player_2                               90 PROVISIONAL

SQL> select name, results
  2     from (
  3     select name, results, rank() over (partition by name order by source) rk
  4             from (select * from tab_a
  5                       union all
  6                   select * from tab_b)
  7     )
  8  where rk = 1;

NAME                              RESULTS
------------------------------ ----------
player_1                               80
player_2                               90
player_3                               30

[levywasike]

thanks alot for your help PAVB and ebrian.

i have tested both methods and the view works in all cases. am using the MINUS method on the live data.