Select Statement Question

[borisbe]

I have a person address table and a person table. A person can have different address types (HOME, MAIL, etc.). Everyone in the database should have a MAIL address type but sometimes that type is not entered.

I know how to include where address type is MAIL but then how do I also get a person in the retrieval where they don't have a MAIL type address?

Person Table

ID
First Name
Last Name, etc.

Person Address Table

ID
Address Type
Address1, etc.

Thank you

 

[dbtoo2001]

Outer Join

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1023213

 

[SantaMufasa]

Borisbe, Welcome to the Oracle fora! We hope you find joy and happiness here.

Could you please post some sample rows (for both the PERSON table and the PERSON_ADDRESS table) that represent specific scenarios that you would like to handle?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[borisbe]

Hi Dave,

Here are some sample rows:

Person Table Person Address Table

001 John Doe 001 MAIL 1413 W 600 N
001 HOME 780 W 900 S

002 Jane Doe 002 HOME 780 W 900 S

003 Minnie Mouse 003 MAIL 565 Van Dr
003 WORK Disneyland
003 HOME 1632 Wild Oak Ln

004 Mickey Mouse 004 WORK Disneyland

I would need the report to retrieve the person records that had a MAIL address type but then also the ones that don't have a MAIL address type. Examples

001 John Doe 001 MAIL 1413 W 600 N
002 Jane Doe 002 HOME 780 W 900 S
003 Minnie Mouse 003 MAIL 565 Van Dr
004 Mickey Mouse 004 WORK Disneyland

or the records that don't have a MAIL address type can have no address showing:

001 John Doe MAIL 1413 W 600 N
002 Jane Doe
003 Minnie Mouse MAIL 565 Van Dr
004 Mickey Mouse


Thanks

 

[SantaMufasa]

Borisbe,

First, judging by your sample-address data, you must live in Utah (as I do). BTW, if you are a Utahn, Oracle is sponsoring a (free) Technology Day tomorrow here in Salt Lake City (if you live nearby). If you wish to attend (and meet up), contact me for details via my signature, below.

Now, to your specific issue...To produce your second set of results (i.e., printing nothing if there is no "MAIL" address), you would use Oracle's "OUTER JOIN" feature (as DBTOO suggested):

select p.id, first_name, address_type, address1
from person p, person_address pa
where nvl(address_type(+),'MAIL') = 'MAIL'
  and p.id = pa.id(+);

ID FIRST_NAME                ADDR ADDRESS1
-- ------------------------- ---- ------------
 1 John Doe                  MAIL 1413 W 600 N
 2 Jane Doe
 3 Minnie Mouse              MAIL 565 Van Dr
 4 Mickey Mouse

Alternately, to produce your first results, you can use this code:

select p.id, first_name, address_type, address1
  from person p
      ,person_address pa
      ,(select id
              ,min(decode(address_type
                         ,'MAIL','1'
                         ,'HOME','2'
                         ,'WORK','3'
                         ,'4')) priority
          from person_address
         group by id) pa_priority
 where p.id = pa.id
   and p.id = pa_priority.id
   and priority = decode (pa.address_type
                         ,'MAIL','1'
                         ,'HOME','2'
                         ,'WORK','3'
                         ,'4')
/

ID FIRST_NAME                ADDR ADDRESS1
-- ------------------------- ---- ------------
 1 John Doe                  MAIL 1413 W 600 N
 2 Jane Doe                  HOME 780 W 900 S
 3 Minnie Mouse              MAIL 565 Van Dr
 4 Mickey Mouse              WORK Disneyland

Let us know if either of these solutions pleases you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[borisbe]

Thanks. I was finally able to try the nvl function and it worked great. I really appreciate the help.