Mysql Three-table Join

[windows]

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....<br /><br /><br />I have 3 tables that I need to join. <br /><br />1. This table keeps track of customers and just assigns each one a unique ID.<br /> CUSTOMERS<br /> CustomerID<br /> Customer_Name<br /><br /><br />2. This table keeps track of Type A proposals for a customer via the foreign key (CustomerID).<br /> TYPEA<br /> A_Proposal_Number<br /> A_Date<br /> A_Price<br /> A_Status<br /> CustomerID <br /><br /><br />3. This table keeps track of Type B proposals for a customer via the foreign key also.<br /> TYPEB<br /> B_Proposal_Number<br /> B_Date<br /> B_Price <br /> B_Status<br /> CustomerID<br /> <br /><br /><br /><b>ASSUMPTIONS: A Customer can have a Type A proposal, a Type B proposal, or both A and B.<br />Status on a proposal can either be Open or Sold. </b><br /><br /><br />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.<br />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! <br /><br />Here is my query:<br /><br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->SELECT Customers.Company_Name,<br /> ?????TypeA.A_Proposal_Number,<br /> ?????TypeA.A_Date, <br /> ?????TypeA.A_Price, <br /> ?????TypeB.B_Proposal_Number<br /> ?????TypeB.B_Date, <br /> ?????TypeB.B_Price<br />FROM Customers ?br />LEFT JOIN TypeA <br /> ???????鐕∟ (Customers.CustomerID = TypeA.CustomerID)<br />LEFT JOIN TypeB <br /> ???????鐕∟ (Customers.CustomerID = TypeB.CustomerID)<br />WHERE (TypeA.A_Status = 'Sold' OR TypeB.B_Status = 'Sold')<!--c2--></div><!--ec2--><br /><br /><br />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<br />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 -------<br /><br />The result set should come up like this in PHPmyAdmin:<br /><br />A_Date ??鐕梍Price ?鐕檕mpany_Name ?鐕榑Date ????B_Price<br />-------- ???--------- ?------------------- ?---------- ???----------<br />2004-06-01 ?100.00 ??ABC Technologies ??NULL ??????NULL<br />NULL ??????NULL ???XYZ Data ????????2004-06-05 ??00.00<br /><br /><br />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. <br /><br /><br /><br />Can someone PLEASE HELP?????? Thanks so much in advance.<br /><br />(sorry for the long post)<!--content-->
Normally I would build this table to make sure my suggestion is accurate, but I have an idea about the problem:<br /><br />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:<br /><br />SELECT * FROM <br />(Customers LEFT JOIN TypeA ON ..)<br />LEFT JOIN TypeB ON...<br /><br />You get the idea. Let me know if that works.<br /><br />-Dave<!--content-->