RE: Select only one of three tables

From: <Witold.Iwaniec_at_atl.bluecross.ca>
Date: Tue, 03 Jul 2001 10:14:34 -0700
Message-ID: <F001.00340542.20010703102300@fatcity.com>

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Witold.Iwaniec_at_atl.bluecross.ca

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Witold.Iwaniec_at_atl.bluecross.ca

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

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Witold.Iwaniec_at_atl.bluecross.ca

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