From Jim.Conboy@trw.com Tue, 03 Jul 2001 11:30:17 -0700 From: "Jim Conboy" Date: Tue, 03 Jul 2001 11:30:17 -0700 Subject: RE: Select only one of three tables Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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 listI have a scenario in which I have to check three tables. If there is recordintable A, take it otherwise check table B, if there is record in table B,takeit otherwise check table C. Let say I am looking for DEPT column and thetablesare DEPT_ONE, DEPT_TWO, and DEPT_THREE. At the end I need only one DEPTcolumn.While I can check each of the tables in order I would like to do it in onestatement. I have tried DECODE but it did not like combination of count andcolumn names - error ORA-00937. To make it simpler here is my query from twotables only:select  decode (count(d2.emp_id), 0, d3.dept, d2.dept) dept  from dept_two d2, dept_three d3where d3.emp_id =  TESTER_1'   and d2.emp_id(+) = d3.emp_idCan someone recommend a solution?ThanksWitold-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author:   INET: Witold.Iwaniec@atl.bluecross.caFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso 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: iain.nicoll@calanais.comFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing).