SQL Query

Posted on 2011-10-31
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
    LVL 7

    Accepted Solution

    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 30

    Assisted Solution

    by:Marco Gasi
    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.state FROM tableA, tableB WHERE BETWEEN data1 AND data2";

    Hope this helps.

    LVL 17

    Assisted Solution

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

    Expert Comment


    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.
    LVL 1

    Author Comment

    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.
    LVL 1

    Author Closing Comment

    Thanks guys.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now