Home » SQL & PL/SQL » SQL & PL/SQL » Query on two tables
Query on two tables [message #283176] Mon, 26 November 2007 05:20 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi all

i want to find out a query which should
retrieve date from first table based on a condition. if no
records found, it should retrieve data from second table
based on the same condition.

Test data

table1:

eno lsal hsal rank
1 100 155 a
2 200 270 b
3 300 350 c

table2:

lsal hsal rank
100 200 aa
201 400 bb

condition:
ex1:
on table1 if lsal and hsal is 100 ge losal and 100 le hsal
then rank a has to be retrieved.

ex2
on table1 if lsal and hsal is 160 ge losal and 160 le hsal
then rank aa has to be retrieved.

so, if data is not retrieved from first table based on
the condition, then it has to retrieve from second table.

Thanks in advance

Nuts
Re: Query on two tables [message #283187 is a reply to message #283176] Mon, 26 November 2007 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if data are present in both tables, you want only the first one? For instance for 110, 250 or 320.

Regards
Michel


Re: Query on two tables [message #283191 is a reply to message #283187] Mon, 26 November 2007 06:02 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Yes Yes Michel

[Updated on: Mon, 26 November 2007 06:02]

Report message to a moderator

Re: Query on two tables [message #283194 is a reply to message #283191] Mon, 26 November 2007 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with data1 as (<select from first table>)
select * from data1
union all
select * from <second table>
where ...
  and not exist (select null from data1)
/

Regards
Michel
Re: Query on two tables [message #283220 is a reply to message #283191] Mon, 26 November 2007 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For example:
SQL> var val varchar2(1);
SQL> exec :val := 'X';

PL/SQL procedure successfully completed.

SQL> with data as (select 1 from dual where dummy=:val)
  2  select * from data
  3  union all
  4  select 2 from dual where not exists (select null from data)
  5  /
         1
----------
         1

1 row selected.

SQL> exec :val := 'Y';

PL/SQL procedure successfully completed.

SQL> with data as (select 1 from dual where dummy=:val)
  2  select * from data
  3  union all
  4  select 2 from dual where not exists (select null from data)
  5  /
         1
----------
         2

1 row selected.

Regards
Michel
Re: Query on two tables [message #283229 is a reply to message #283220] Mon, 26 November 2007 06:59 Go to previous message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Thanks Michel Smile
Previous Topic: CREATE A TRIGGER
Next Topic: Query doubt
Goto Forum:
  


Current Time: Sat Dec 10 11:14:07 CST 2016

Total time taken to generate the page: 0.13973 seconds