mysql join question

[Anon]

Hello all,

I'm a little shaky on mysql joins so bare with me. First let me briefly explain my table layout.

I have a table "customers", that holds customer information such as phone numbers and email. A customer can be one of three types: residential, commercial, or new construction. So in the customers table there is either a residential_id, commerical_id, or new_construction_id linking the customer to that particular table. For example, let's say I have a customer who's commercial_id and new construction_id is NULL, but the residential_id is not NULL, then that customer should be linked to the residential table.

I am wondering if there is a way to extract the information I want from the db in one sql statement. Right now, I have two sql statements, one that pulls out all three id's from the customer table and another that then links the customer with the appropriate table. In other words, I check which id is not NULL then using if statments, I query the database with the correct sql.

I feel like there should be a way to do this in one statement with a join. I've tried looking up different join statements but to no avail. None of them seem to do what I need.

If I was too brief in my explanation, I can post the table layouts and some of my code. There is probably an obvious solution so I'll hold off until someone asks. One more thing, if there isn't a way to combine the sql statements, is there more efficient solution? Perhaps my layout is poor and can be improved upon?

Thanks in advance,
Samantha

[Anon]

Hi Samantha

You're trying to join Customer with the other 3 tables?

You can

select customer., table1.
from customer, table1
where customer.residential_id = table1.id
and customer.residential_id is not null
union
select customer., table2.
from customer, table2
where customer.residential_id = table1.id
and customer.commerical_id is not null
union
....

The only restrictions with union is that the result structure of each part must be the same.

[Anon]

I'm sorry, no I'm not trying to do that. I'm trying to join the customer table with only one of the three other tables.

Here is the simplified table layout.

Customers

customer_id
email
fax
residential_id
commercial_id
new_construction_id

Residential

residential_id
first_name
last_name

Commercial

commercial_id
property_manager
contact
title

New_Construction

new_construction_id
builder
contact
homeowner

I'm trying to get all the customer's information in one sql statement. A customer can only be one of the three types: residential, commercial, and new construction.

If the residential_id is NOT NULL, it should be joined with the residential table. If the commercial_id is NOT NULL it should be joined with the commercial table and so on.

Can I do this is one statement, or should I change my table layout?

Hope this explains my problem better.
-Samantha

[Anon]

I should also note that I don't want to return every field from all three tables even if they are null. I would think that it would be inefficent if I had thousands of customers. But correct me if I am wrong.

-Samantha