Brian,
Identical area codes will not get you nearest neighbours in cases of metro
areas with multple area codes, or in cases of nearby companies on different
sides of area code boundaries. For nearness, you need need to plug in
geographical data, I think. On your approach, though, are you looking for
something like ...
SELECT ...
FROM table3
INNER JOIN table4
ON SubString( table3.customer_phone)=table4.prefix
INNER JOIN table2
ON SubString(table3.customer_phone,1,3)=table2.company_area_code
INNER JOIN table1 ON table2.company_id=table1.company_id
WHERE table1.company.id=#;
?
PB
----- Original Message -----
From: Duke, Brian
To: [EMAIL PROTECTED]
Sent: Sunday, March 21, 2004 3:16 AM
Subject: Tough Query for this New DBA
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]
