Home » SQL & PL/SQL » SQL & PL/SQL » Retrieving data from table which has higher precedence (Oracle 11g)
Retrieving data from table which has higher precedence [message #298173] Tue, 05 February 2008 04:10 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Friends,

I need clarification regarding retrieving
data from multiple table.

Suppose, we have three tables T1, T2, T3 in the
order of precedence. Preference in the sense if we have
record in T1,T2 and T3, we give preference for T1 record.
If we have record in T2 and T3 only, we give preference for
record in T2.

What kind of technique should we use to retrieve the
appropriate data from the tables?

Thanks in Advance
Natesh
Re: Retrieving data from table which has higher precedence [message #298176 is a reply to message #298173] Tue, 05 February 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the query, its complexity, and so much things.
You can query T1, if it is not there query T2 and so on, for instance.

Regards
Michel
Re: Retrieving data from table which has higher precedence [message #298179 is a reply to message #298176] Tue, 05 February 2008 04:17 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Michel,

Thanks for your reply.
Currently i am following the same way which you
have mentioned. Cant we have a simple one?

Regards
Natesh
Re: Retrieving data from table which has higher precedence [message #298180 is a reply to message #298179] Tue, 05 February 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Simple? simple following which criteria?
You mean less statement? Yes it may be possible but is this the right way? I don't think so.

Regards
Michel
Re: Retrieving data from table which has higher precedence [message #298184 is a reply to message #298180] Tue, 05 February 2008 04:26 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

Currently i am using 3 select statements for each table
and overwriting the previous values, if any.

Instead of using three different select's,
cant we make it using single select statement.

Natesh
Re: Retrieving data from table which has higher precedence [message #298185 is a reply to message #298184] Tue, 05 February 2008 04:35 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
You can, but as Michel is trying to tell you: there is no such thing as THE ULTIMATE QUERY for your problem.

It all depends on your requirements, which by the way we don't know anything about.

If it works fine with your three statements, why don't you stay with that solution?

Re: Retrieving data from table which has higher precedence [message #298225 is a reply to message #298185] Tue, 05 February 2008 06:46 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Ok. Thanks Marc
Re: Retrieving data from table which has higher precedence [message #298228 is a reply to message #298225] Tue, 05 February 2008 07:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Another solution could be to union the tables with a sort that represents the precedence, and then select the first row of that union.

Untested example :

select value from (
    select 1, value from t1 where ...;
    union 
    select 2, value from t2 where ...;
    union
    select 3, value from t3 where ...;
    order by 1
)
where rownum = 1;


There would be fewer parse/fetch, but the query execution itself might take longer.

Re: Retrieving data from table which has higher precedence [message #298230 is a reply to message #298228] Tue, 05 February 2008 07:11 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There would be fewer parse/fetch, but the query execution itself might take longer.

Parse and fetch time will likely be tiny against query lengthening. Think, you ALWAYS query the 3 tables instead of 1 to 3.

Don't use UNION, use UNION ALL.

Regards
Michel

Previous Topic: For Loop coding problem
Next Topic: Trigger - bad bind PLS-00049
Goto Forum:
  


Current Time: Thu Dec 08 20:21:32 CST 2016

Total time taken to generate the page: 0.13158 seconds