Viktor wrote: > > Hello All, > > It looks as if I've hit a brick wall and I'd very much > appreciate if you can help. > > desc Names > > FIRST_INIT NOT NULL CHAR(4) > SECOND_INIT NOT NULL CHAR(1) > INIT_SEQUENCE NOT NULL NUMBER > LAST_NAME VARCHAR2(30) > FIRST_NAME VARCHAR2(20) > FLAG NOT NULL NUMBER > > desc MEMBER > > MEM_FIRST_INIT CHAR(4) > MEM_SECOND_INIT CHAR(1) > MEM_INIT_SEQUENCE NUMBER > > Member table references Names table on FIRST_INIT, > SECOND_INIT, INIT_SEQUENCE (FOREIGN KEYS). > > Names table has NOT NULL column flag. It can only be 0 > or 1 -- means Name is a member. > > But, not all NAME records with FLAG 1 are in MEMBER. > In other words, records in MEMBER usually represent > other some other types of memership. > > But, in this case, I need to get those NAME(parent) > records that have FLAG = 1, and those MEMBER(child) > records that reference NAMES via foreign keys. > Sometimes NAME record with FLAG = 1, also has a MEMBER > record, and it could be that record with FLAG = 1 does > not have a record in MEMBER. > > I need all those with FLAG = 1 in NAMES + all records > that are in MEMBER. > > Is there another way besides the UNION: > > SELECT a.first_init||a.second_init||a.init_sequence > INITIALS, > a.last_name LAST_NAME, > a.first_name FIRST_NAME, > a.flag MEMBER_FLAG, > FROM names a > WHERE a.advisor_flag = 1 > UNION > SELECT a.first_init||a.second_init||a.init_sequence > INITIALS, > a.last_name LAST_NAME, > a.first_name FIRST_NAME, > a.flag MEMBER_FLAG > FROM names a, > members m > WHERE a.first_init = m.mem_first_init > and a.second_init = m.mem_second_init > and a.init_sequence = m.mem_init_sequence > > Thanks a lot in advance!!!!! > > Regards, > > Viktor >
The UNION is perfect. You could possibly do something with external joins and an 'OR', but it would not be more legible. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
