Re: SQL Question (DISREGARD 1ST MESSAGE, SORRY)
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_NAMEVARCHAR2(30) FIRST_NAME VARCHAR2(20) FLAGNOT NULL NUMBER desc MEMBER MEM_FIRST_INIT CHAR(4) MEM_SECOND_INIT CHAR(1) MEM_INIT_SEQUENCENUMBER 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).
SQL Question (DISREGARD 1ST MESSAGE, SORRY)
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_NAMEVARCHAR2(30) FIRST_NAME VARCHAR2(20) FLAGNOT NULL NUMBER desc MEMBER MEM_FIRST_INIT CHAR(4) MEM_SECOND_INIT CHAR(1) MEM_INIT_SEQUENCENUMBER 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: [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).