Re: SQL select statement challenge

Hi, Thomas.

        Yes, this is possible. Try something like this:

select table1.info "Info", person_c.name "Customer Name",

   person_c.phone "Customer Phone", person_c.address    "Customer Address", person_s.name "Supplier Name",    person_s.phone "Supplier Phone", person_s.address    "Supplier Address"
from

   table1, person person_c, person person_s, cust_link, supp_link where

   table1.primary_key = cust_link.key(+) and    cust_link.foreign_key = person_c.person_key(+) and    table1.primary_key = supp_link.key(+) and    supp_link.foreign_key = person_s.person_key(+)

Hope this helps,
-Jeff

On Tue, 02 Nov 1999 10:50:14 +0000, Thomas Nunn <tom_at_tnunn.demon.co.uk> wrote:

>Ok, I've come into a little SQL problem that I'm sure there must be a
>solution to.
>
>Ok, here goes.
>
>Say I have one large table that basically holds information about
>people, now these people can be one of two different types of person
>(say.. customers and suppliers), or both. Now I have two linking tables
>that link another table with two of these people, one of each type
>(remember, they can be the same person).
>
>So basically, here's the setup:
>
>Table one
>-------------------
>| Primary key |
>| Other misc. data |
>-------------------
>
>Person Table
>
>-----------------
>| Person key |
>| Phone Number |
>| Address.. etc.. |
>------------------
>
>Link table for customers
>
>--------------------------------------
>| Table 1 Key |
>| Customer key (foreign from people) |
>--------------------------------------
>
>Link table for suppliers
>
>--------------------------------------
>| Table 1 key |
>| Supplier key (foreign from people) |
>--------------------------------------
>
>Anyway, I'm pretty new to SQL and the documentation has been confusing
>be a little.
>
>I just need to know how to write the select statement that will return
>all the data from table 1 as well as the data from the linking tables,
>plus the two relevent people from the person table. I also need it to
>return an appropriate number of blank spaces if the requested person
>doesn't exist in the file.
>
>Is this possible?
>
>I know that it would be far simpler to have the two types of people in
>seperate tables, but in the application I'm writing, these two people
>are very often the same person.
>
>Any help is much appreciated.
>
>Thomas Nunn.
>
Received on Mon Nov 15 1999 - 12:25:12 CST