querying two tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

querying two tables

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2008-05-23 : 18:39:54

This is my problem

Table A Columns:

OriginatingNumber,
TerminatingNumber

Both of these columns contain npanxx numbers which would be the first 6 digits of a phone number, example 217345 which is in illinois.

Table B Columns:

npanxx,
state

table B hold all npanxx numbers in the united states and what state it pertains to.

what i need to do is run a query to check and see what state the OriginatingNumber and the TerminatingNumber are in and decide if both numbers passed are in the same state or not.

so for example in Table A Row 1: OriginatingNumber is 217345 and the TerminatingNumber is 309454. I need to check both of these numbers against table B and it would return that both numbers are in IL. Basically I need to return a result of yes or no. yes they are in the same state or no they are not in the same state.

Any help would be great

Thanks for reading,

Nick

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-23 : 19:12:48

Something like this maybe? of course you gotta watch for any NULLS
Select OriginatingNumber,
TerminatingNumber
SameState = (CASE
WHEN OriginatingNumber = TerminatingNumber Then 1
WHEN OriginatingNumber <> TerminatingNumber THEN 0
),
OrigState = (SELECT DISTINCT state FROM B state WHERE OrigTerm.OriginatingNumber = state.npanxx),
TermState = (SELECT DISTINCT state FROM B state WHERE OrigTerm.TerminatingNumber = state.npanxx)
From A OrigTerm
try it and see?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-24 : 00:55:40

[code]SELECT a.OriginatingNumber,
b.state as 'OriginatingState',
a.TerminatingNumber,
c.state as 'TerminatingState',
CASE WHEN b.state = c.state THEN 1 ELSE 0 END AS SameState
FROM TableA a
INNER JOIN TableB b
ON b.npanxx=a.OriginatingNumber
INNER JOIN TableB c
ON c.npanxx=a.TerminatingNumber[/code]

nhess80
Yak Posting Veteran

83 Posts

Posted - 2008-05-27 : 14:27:09

Thanks for the help...I think I got it figured out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources