On May 31, 2004, at 12:14 AM, Tom Benson wrote:
story is I've been given a MySQL database to build a front end for....
a bit of a strange layout.... here's where I hit the wall...
Customer DB is split into numerous tables:
CUSTOMERS
PEOPLE
COMPANIES
PHONENUMBERS
ADDRESSES
Basically the CUSTOMERS table is the key table, containing a column of
values that all other tables key against. My problem is my client
wants to be able to list all of his customers in a listbox, not just
PEOPLE, and not just COMPANIES. And he wants this information
alongside addresses and phonenumbers. To do this I'm going to have to
conditionally join tables me thinks, and I'm running into
difficulties.
Here's my basic query. It doesn't throw any errors, but send MySQL off
on a trip to the moon and back... have left it running on a test data
suite of 50 records for hours with no result....
I'm guessing my problem is using the OR clause in an inner join, but
SELECT * FROM customers, people, companies, addresses, phonenumbers
WHERE customers.CustomerID=people.CustomerID OR
customers.CustomerID=companies.CustomerID AND
customers.CustomerID=addresses.CustomerID AND
phonenumbers.CustomerID=customers.CustomerID
I know I can do this with multiple queries, but I also know that there
is a way to do this, and would like to broaden my SQLskills a bit....
You'll get a cartesian product out of this.
It's going to try and give you every column from every table where
either of those conditions match.
It's not surprising.
What columns do PEOPLE and COMPANIES have ?
That's the crux and once I know that then we can probably figure a way
to do this.
_______________________________________________
Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
|