Oracle-L: SQL Question (DISREGARD 1ST MESSAGE, SORRY)

From: Viktor <stant_98_at_YAHOO.COM>
Date: Fri, 07 Jun 2002 21:53:18 -0800
Message-ID: <F001.0047857E.20020607215318@fatcity.com>

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

Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com
--

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

Author: Viktor
INET: stant_98_at_yahoo.com

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). Received on Sat Jun 08 2002 - 00:53:18 CDT