Solved

# SQL Query

Posted on 2011-10-31
Medium Priority
362 Views
I have tableA
date|phone1|ext

tableB
areacode|prefix|city|state

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
714|555|moralton|texas

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.
0
Question by:livegirllove

LVL 7

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?

armand
0

LVL 31

Assisted Solution

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.

Cheers
0

LVL 18

Assisted Solution

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)
0

LVL 7

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.
0

LVL 1

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.
0

LVL 1

Author Closing Comment

ID: 37073479
Thanks guys.
0

## Featured Post

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