Following select
select * from person p, link_customer l
where p. id = l.personid
union
select * from person p, link_supplier l
where p. id = l.personid
returns one table with all persons being customers and
all persons being supplier
Philippe
> 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.
>
>
>
--
This answer is courtesy of QuestionExchange.com
http://www.questionexchange.com/showUsenetGuest.jhtml?ans_id=8679&cus_id=USENET&qtn_id=7353
Received on Mon Dec 13 1999 - 09:41:37 CST
