SQL select statement challenge

From: Thomas Nunn <tom_at_tnunn.demon.co.uk>
Date: Tue, 02 Nov 1999 10:50:14 +0000
Message-ID: <381EC1E6.BDDA3AC8@tnunn.demon.co.uk>

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 Tue Nov 02 1999 - 04:50:14 CST