Searching for info from multiple tables

[GabeS]

I have 3 tables that I am searching through. There is a people table, a phone numbers table and an address table. The user is inputting information from a web site and runnint the pl/sql procedure. My question is: what would be a select statement that would search for the first name of a person, then print out all info from the phone and address table that has the same id (primary key) as the id of the row with the person in it. Thanks in advance.

 

[r937]

select person.firstname
     , phone.*
     , address.*
  from person
left outer
  join phone
    on person.id = phone.id
left outer
  join address
    on person.id = address.id

the unfortunate "whoopsie" in the above is that if a person has multiple phones and multiple addresses, then there will be a cross-join effect, and each phone will be paired with every address

rudy

http://rudy.ca/

 

[GabeS]

Thanks for the quick answer. Unfortunately, many people will have multiple addresses/phone numbers. Also, since this is for web output, I just found out that you can't select * into a varchar2 variable.

 

[r937]

> you can't select * into a varchar2 variable

well, no, you would have to concatenate the values yourself

if people have multiple phones and multiple addresses, you're not going to want to do a join

however, a UNION will work nicely

select person.id        as recid
     , 1                as recsort
     , '   Name:'       as datatype
     , person.firstname as datavalue
  from person
 where person.firstname LIKE '%foo%'
UNION ALL
select phone.personid
     , 2                
     , '  Phone:'       
     , phone.areacode ||' '|| 
       phone.exchange   
  from phone
 where phone.personid
    in ( select person.id   
           from person
          where person.firstname 
           LIKE '%foo%' )
UNION ALL
select address.personid
     , 3                
     , 'Address:'       
     , address.addrline1 ||' '|| 
       address.addrline1 ||' '|| 
       address.city ||' '|| 
       address.province
  from address
 where address.personid
    in ( select person.id   
           from person
          where person.firstname 
           LIKE '%foo%' )
ORDER 
    BY recid, recsort

rudy

 

[angiole]

Interesting approach.

Glad you made sense of what he was asking.

Having seen what you did will also now allow you to concatenate the Name/Phone/Address info across the row to reduce the amount of output.

AA 8~)

 

[GabeS]

Hmm. I'm just learning SQL, so I don't understand all of the second response, but I think I get the basic idea. I'm not sure if this will work with PL/SQL, but there was no PL/SQL forum, so this was as close as I could get. If all of this is not possible in PL/SQL, I'm sorry to have bothered you.

 

[ThomVF]

This will work in PL/SQL.

For PL/SQL questions use the ORALCE8i or ORACLE9i forums.

How are you getting it back to the web site (what toolset and database connection mechansism)?
"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[GabeS]

I have apache webserver. ( I think that's what you were asking for.)

 

[ThomVF]

PL/SQL is going to query your data fine, but unless you store it somewhere, push it to a file, or have a recordset established in some tool, how do yo plan to get it to your webserver for display ? (I am not an Apache person) "Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[r937]

angiole, you gotta slow down, man!!

"Having seen what you did will also now allow you to concatenate the Name/Phone/Address info across the row to reduce the amount of output."

well, no, the amount of output is the same

what if there are five addresses? you want them to wrap??!! ewwww

i prefer

   Name: Joe Blow
  Phone: (212) 555-1212
Address: 123 Main Street, Toronto, KS
Address: 456 Sesame Street, New York, NY
 
   Name: Bob NoPhone
Address: 123 Main Street, Toronto, KS[code]

and so on

much more orderly

:-)

 

[GabeS]

There is a package that allows you to print directly to html/a browser. I user htp.print(stuff to print) or htp.prints(stuff without html tags)

 

[ThomVF]

You are going to have to manually spool the results to standard output. PL/SQL does not stream its output by default.

You might need to create a cursor with the query, then loop through the cursor records, using &quot;DBMS_OUTPUT.PUT_LINE(<record field values>)&quot;

Not sure if you knew this already. &quot;Helping others to help themselves...&quot;
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[angiole]

I was just proposing another layout option since this info would be presented to a user.

So a search for 'John' would return

Name       Phone           Address               City
---------- --------------- --------------------- -------

John Doe   (123)456-7890   123 Here St           City2
John Doe   (234)456-7891   456 There St          City32
John Doe   (345)456-7892   7890 Everywhere St    City42
John Foe   (010)010-1010   8345 Qwerty           City78
John Fox   (020)020-0202   0202 Asdfg            City2

I'm not a web person, programming wise, but user wise, I would expect the above to be presented in a table format that would allow me to resort the list on the fly.

Remember too, we should try to save the trees. You can make an address print on 5 lines, giving you 10 address per print page, or 50 addresses per print page.

Printing this stuff wasn't specified in the original request, but if the users are anything like the ones I've supported, it's bound to all hit the paper eventually.

As well, no one has questionned why GabeS is querying on 'First Name'. There is a reason phone books are not sorted this way.

Good talking to you guys (girls?, why not).


AA 8~)

 

[r937]

oh, i totally agree about the trees, and nothing wrong with being creative about how stuff is laid out

but multiple phones and multiple addresses just do not lend themselves to your type of layout, angiole, despite your best efforts

your three examples for John Doe seem to associate a phone with an address, and that would be wrong, wrong, wrong, the way those tables are designed

(something about denormalizing from 4th normal form, or something -- i'm too lazy to look it up)

bad or misleading data is a bigger crime in my book than a layout that might take a bit more paper to print

if John Doe really has 3 phone numbers and 3 addresses, how do you pick which phone number to print beside which address? this is why i said earlier that a join wouldn't work

arranging data on the page to reflect accurately the real relationships in the database is of higher concern than whether users end up printing it, although that's a legitimate concern too

rudy

 

[angiole]

There are many uncertainties here...

a) GabeS really knows what he is asking for (no reflection on you GabeS, just the reality of third party assistance)
b) The table designs
c) The actual data
d) What the data in it's output format should accomplish

I guess this horse is dead now... AA 8~)