| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Select only one of three tables
Witold.Iwaniec_at_atl.bluecross.ca wrote:
>
> Hi Remco
>
> Thanks for the reply. There can be more than one record that I should get. I
> guess I could have been clearer - the rule is:
> if there is one or more records in dept_one, return all matching records from
> dept_one and don't look at other tables.
> If there is no record in dept_one, check dept two and if there is one or more
> records in dept_two, return the matching records and don't check dept three.
> If there are no records in dept_two (so also no records in dept_one) check
> dept_three
>
> Originally I thought about decode but it will not work. Ater reading the
> responses I think about using union with something like:
>
> select dept from dept_one
> where emp_id = TESTER_1'
> UNION
> select dept from dept_two
> where emp_id = TESTER_1'
> and not exists (select 1 from dept_one where emp_id = 'TESTER_1')
> UNION
> select dept from dept_three
> where emp_id = TESTER_1'
> and not exists (select 1 from dept_one where emp_id = 'TESTER_1')
> and not exists (select 1 from dept_two where emp_id = 'TESTER_1')
>
> It should work but I have to look at performance. All I want is save some
> network traffic - calls from application to the server
>
> Thanks
>
> Witold
>
> "Daemen, Remco" <R.Daemen_at_facent.nl> on 07/03/2001 14:17:41
>
> Please respond to ORACLE-L_at_fatcity.com
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc: (bcc: Witold Iwaniec/ATL_BLUECROSS_CA)
>
> Hi Witold,
>
> Try this:
>
> select * from
> (
> select dept from dept_one
> union all
> select dept from dept_two
> union all
> select dept from dept_three
> )
> where rownum <=1 ;
>
> HTH, Remco
>
> -----Oorspronkelijk bericht-----
> Van: Witold.Iwaniec_at_atl.bluecross.ca
> [mailto:Witold.Iwaniec_at_atl.bluecross.ca]
> Verzonden: dinsdag 3 juli 2001 18:37
> Aan: Multiple recipients of list ORACLE-L
> Onderwerp: RE: Select only one of three tables
>
> Tom
>
> Thanks for the reply.
> The UNION would be good if I wanted all dept values from the tables. But the
> rule is more complex - if there is eg. one record in table DEPT_ONE, I have
> to
> get back only this one record even though there may other/more records for
> the
> same employee in the other tables.
> If there is no record in table DEPT_ONE and there is rcord in DEPT_TWO - I
> want
> back only what is in DEPT_TWO, regardless of what is in DEPT_THREE.
>
> Witold
>
> "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us> on 07/03/2001 13:01:51
>
> Please respond to ORACLE-L_at_fatcity.com
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc: (bcc: Witold Iwaniec/ATL_BLUECROSS_CA)
>
> Witold,
>
> have you tried using the UNION operator? like:
>
> select dept
> from dept_one
> union
> select dept
> from dept_two
> union
> select dept
> from dept_three
> order by 1;
>
> this will give you only one occurrence of the value of dept from all three
> tables.
>
> hope this helps
>
> Tom Mercadante
> Oracle Certified Professional
>
> -----Original Message-----
> [mailto:Witold.Iwaniec_at_atl.bluecross.ca]
> Sent: Tuesday, July 03, 2001 10:11 AM
> To: Multiple recipients of list ORACLE-L
>
> Hello list
>
> I have a scenario in which I have to check three tables. If there is record
> in
> table A, take it otherwise check table B, if there is record in table B,
> take
> it otherwise check table C. Let say I am looking for DEPT column and the
> tables
> are DEPT_ONE, DEPT_TWO, and DEPT_THREE. At the end I need only one DEPT
> column.
>
> While I can check each of the tables in order I would like to do it in one
> statement. I have tried DECODE but it did not like combination of count and
> column names - error ORA-00937. To make it simpler here is my query from two
> tables only:
>
> select decode (count(d2.emp_id), 0, d3.dept, d2.dept) dept
> from dept_two d2, dept_three d3
> where d3.emp_id = TESTER_1'
> and d2.emp_id(+) = d3.emp_id
>
> Can someone recommend a solution?
>
> Thanks
>
> Witold
>
Witold,
select whatever
from table_3
where condition
and not exists (select 'x'
from table_2
where condition
union
select 'x'
from table_1
where condition)
and not exists (select 'x'
from table_1
where condition)
-- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax: +44 (0) 7050-696-449 Performance Tools & Free Scripts -------------------------------------------------------------- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -------------------------------------------------------------- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: sfaroult_at_oriole.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Jul 03 2001 - 13:50:23 CDT
 |
 |