SQL Query

Posted on 2011-10-31
Medium Priority
Last Modified: 2012-05-12
I have tableA


I want to display a list of all data in a date range but add in the city/state of the phone1 field.

Phone1 data will look like 7145551212
the matching row in tableB would have

so i suppose I need to first pull out the first 3 digits to match against areacode and then the next 3 digits to match prefix and display the matching city/state in the result.

some help with the code would be great.
Question by:livegirllove

Accepted Solution

armchang earned 1200 total points
ID: 37061053
hi livegirllove,

Run this sql script on your database:

SELECT date, phone1, ext, areacode, prefix, city, state FROM tablea LEFT JOIN tableb ON
 (SUBSTRING( phone1, 1, 3 ) = areacode ) AND (SUBSTRING( phone1, 4, 3 ) =prefix)
WHERE date BETWEEN '2011-11-01' AND '2011-11-01';

Let me know how was it?

LVL 31

Assisted Solution

by:Marco Gasi
Marco Gasi earned 400 total points
ID: 37061059
I'm not sure your database structure makes sense: if you have no technical reason to do that, I would expect to have phone number in tableB with an auto-increment id whereas in tableA I would expect to find a field which refers to the id field of tableA...

Anyway, you could do something like this:

$sql="SELECT tableB..areacode, tableB.prefix, tableA.phone1, tableB.city, tableB.state FROM tableA, tableB WHERE tableA.date BETWEEN data1 AND data2";

Hope this helps.

LVL 18

Assisted Solution

by:Garry Glendown
Garry Glendown earned 400 total points
ID: 37061060
You could just use the substring() function in sql to join the two tables ... simplest form might look something like this:

select date, phone1, ext, city, state from tableA a, tableB b where b.areacode = substr(a.phone1,3) and b.prefix = substr(a.phone1,4,3)

(requires that all possible areacode/prefix combinations are present ... calls to none-existing tableB entries would not be listed)
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 37061134

From Garry-G: (requires that all possible areacode/prefix combinations are present ... calls to none-existing tableB entries would not be listed)

That's why LEFT JOIN is better to use than the INNER JOIN or what you're using now a TABLE JOIN.

Author Comment

ID: 37061523
thanks everyone.  Ill do some playing around and post back.
I dont have control over the files.  We get access to the phone system log postgresql db.  I have a DB with areacode -> city.  I'm tasked with mashing them together.

Author Closing Comment

ID: 37073479
Thanks guys.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month17 days, 1 hour left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question