Mysql Three-table Join

[Sarah]

Hopefully the family will be able to help me out on this one. It's probably something really simple but I am ripping my hair out over it. PLEASE HELP!!!!!!!!Here's the deal (simplified version of course) I will explain the best I can....

 

 

I have 3 tables that I need to join.

 

1. This table keeps track of customers and just assigns each one a unique ID.

CUSTOMERS

CustomerID

Customer_Name

 

 

2. This table keeps track of Type A proposals for a customer via the foreign key (CustomerID).

TYPEA

A_Proposal_Number

A_Date

A_Price

A_Status

CustomerID

 

 

3. This table keeps track of Type B proposals for a customer via the foreign key also.

TYPEB

B_Proposal_Number

B_Date

B_Price

B_Status

CustomerID

 

 

 

ASSUMPTIONS: A Customer can have a Type A proposal, a Type B proposal, or both A and B.

Status on a proposal can either be Open or Sold.

 

 

I want to print out a report that simply shows the Customer and the corresponding A or B proposal information, but only if they are sold proposals.

My problem is that I can print out the information if the customer has only an A proposal, or a B proposal, but if they have one of each, it won't print correctly!

 

Here is my query:

 

>SELECT Customers.Company_Name,
          TypeA.A_Proposal_Number,
          TypeA.A_Date, 
          TypeA.A_Price, 
          TypeB.B_Proposal_Number
          TypeB.B_Date, 
          TypeB.B_Price
FROM Customers  
LEFT JOIN TypeA 
               ON (Customers.CustomerID = TypeA.CustomerID)
LEFT JOIN TypeB 
               ON (Customers.CustomerID = TypeB.CustomerID)
WHERE (TypeA.A_Status = 'Sold' OR TypeB.B_Status = 'Sold')

 

 

For instance: I have a customer with a CustomerID of 5 called ABC Technologies that has one sold TypeA proposal numbered A00001 dated 6-1-04 for $100.00

and another customer with an ID of 10 called XYZ Data that has one sold Type B proposal numbered B00002 dated 6-5-04 for $500.00 -------

 

The result set should come up like this in PHPmyAdmin:

 

A_Date      A_Price    Company_Name    B_Date         B_Price

--------       ---------   -------------------   ----------       ----------

2004-06-01   100.00     ABC Technologies     NULL             NULL

NULL             NULL       XYZ Data                 2004-06-05    500.00

 

 

But like I said before if I have say, ABC Technologies with an A proposal and a B proposal it can't differentiate and doesnt separate them.

 

 

 

Can someone PLEASE HELP?????? Thanks so much in advance.

 

(sorry for the long post)

[dmohs]

Normally I would build this table to make sure my suggestion is accurate, but I have an idea about the problem:

 

I believe you essentially have a problem with parenthesis. Right now, you are saying, "For each customer, get existing TypeA and TypeB proposals." What you should be saying is, "For each customer, get the TypeA proposal if it exists. Then, take the resulting set of customers and TypeAs and get the TypeB proposals if they exist." Use parenthesis to say this to MySQL:

 

SELECT * FROM

(Customers LEFT JOIN TypeA ON ..)

LEFT JOIN TypeB ON...

 

You get the idea. Let me know if that works.

 

-Dave