RE: Select only one of three tables

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
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: 
  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: Daemen, Remco
  INET: R.Daemen_at_facent.nl

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