asked on

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.

ASKER CERTIFIED SOLUTION

Armand G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Marco Gasi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Garry Glendown

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Armand G

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.

livegirllove

ASKER

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.

livegirllove

ASKER

Thanks guys.