RE: Select only one of three tables

[Jim Conboy]

This could get ugly.  I'm thinking the decode/outer join method won't work because there's no table you can reliably use as a base for the outer join.  How about:   select emp_id, dept from dept_one union (select emp_id, dept from dept_two minus select emp_id, dept from dept_one) union (select emp_id, dept from dept_three minus         (select emp_id, dept from dept_two union select emp_id, dept from dept_one)) /   and then go for some coffee if these tables are large at all.   Jim     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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Nicoll, Iain (Calanais)   INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).