SQL Query

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.
LVL 1
livegirlloveAsked:
Who is Participating?
 
armchangConnect With a Mentor Commented:
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
 
Marco GasiConnect With a Mentor FreelancerCommented:
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
 
Garry GlendownConnect With a Mentor Consulting and Network/Security SpecialistCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
armchangCommented:

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
 
livegirlloveAuthor Commented:
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
 
livegirlloveAuthor Commented:
Thanks guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.