There has got to be a better way for me to query/subquery this data. I have 4 tables.
Table1 ------------------------- Company_Name | Company_ID ------------------------- Blue Shoe | 101 Fast Cow | 102 Table2 ------------------------------------------------------------------------ --------- Company_ID | Company_Location | Company_State | Company_area_code | Company_Prefix ------------------------------------------------------------------------ ---------- 101 | davetown | CO | 303 | 827 102 | bobville | AZ | 501 | 666 Table3 ------------------------------------------------- Customer_Location | Customer_Phone | Customer_ID ------------------------------------------------- bobville | 3035551234 | 201 davetown | 5010004321 | 202 Table4 -------------------------------------------------- State | Area_Code | Prefix | Phone_Company -------------------------------------------------- CO | 303 | 827 | qwerst AZ | 501 | 666 | qwerst I'm having trouble with a subquery SELECT Table1.Company_Name , Table2.Company_ID , Table2.Company_Location FROM Table1, Table2, Table3 WHERE ( Table2.Company_Prefix = (SELECT Table4.Prefix FROM Table4 WHERE (SUBSTRING(Table3.Customer_Phone,4,3) = Table4.Area_Code) AND Table2.Company_Area_Code = (SELECT Table4.Area_Code FROM Table4 WHERE (SUBSTRING(Table3.Customer_Phone,1,3) = Table4.Area_Code) AND Table2.Company_ID = Table1.Company_ID ); the problem is with my query. I want to select the company nearest the customer. I have tried subqueries but I somehow don't have my syntax correct. Could someone give a suggestion on the best way to query all four tables in one statement? Thanks, bd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
