I'm stuck on this problem - Msg 4104, Level 16, State 1, Line 25 The multi-part identifier "pp.PhoneNumber" could not be bound

Question

I'm stuck on this problem - Msg 4104, Level 16, State 1, Line 25 The multi-part identifier "pp.PhoneNumber" could not be bound

andy.julson

Old Hand

Points: 398
More actions
December 29, 2011 at 7:49 pm

#391223

CREATE TABLE SUBSCRIBERS
(FirstNameVarChar(50),
LastNameVarChar(50),
PhoneNumberVarChar(20),
HouseNumberChar(10),
PredirectionalChar(2),
StreetNameChar(40),
StreetSuffixChar(5),
PostDirectionalChar(2),
UnitNumberChar(8),
CityChar(30),
StateChar(2),
ZipCodeChar(5),
ZipExtensionChar(4),
ExternalAccountIDInt,
EmailVarChar(50),
D1Int,
D2Int,
D3Int,
D4Int,
D5Int,
D6Int,
D7Int)
INSERT INTO SUBSCRIBERS (FirstName, LastName, PhoneNumber, HouseNumber, Predirectional, StreetName, StreetSuffix, PostDirectional, UnitNumber,
City, State, ZipCode, ZipExtension, ExternalAccountID, Email, D1, D2, D3, D4, D5, D6, D7)
SELECT hp.FirstName, hp.Name, pp.PhoneNumber, ad.HouseNo, ad.PreDir, ad.StreetNm, ad.StreetTp, ad.PostDir, ad.UnitType, ad.UnitNo, ad.CityNm,
ad.StateID, ad.ZipCode, ad.ZipCode4, sub.subscriptionid, hp.EMailAddress, pd.D1, pd.D2. pd.D3, pd.D4, pd.D5, pd.D6, pd.D7
FROM dbo_HomePhone hp, dbo_Subscription sub, dbo_Address_G1 ad, dbo_PhoneDelivery pd
LEFT OUTER JOIN dbo_PersonPhone pp
ON hp.PhoneID = pp.PhoneID
WHERE hp.PhoneID = pd.PhoneID and
pd.AddressID = ad.AddressID and
hp.PhoneID = sub.PhoneID and
pd.EndDate > GETDATE() and
pd.StartDate < GETDATE() and
sub.EndDate > GETDATE()and
sub.StartDate < GETDATE()
SELECT * FROM SUBSCRIBERS
DROP TABLE SUBSCRIBERS
What am I doing wrong?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Can you provide the DDL for the personphone table?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

It's not relevant to your error but you should rewrite the JOIN as well. It may be a CROSS JOIN.

FROM dbo_HomePhone hp, dbo_Subscription sub, dbo_Address_G1 ad, dbo_PhoneDelivery pd

LEFT OUTER JOIN dbo_PersonPhone pp

ON hp.PhoneID = pp.PhoneID

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Does the table dbo_PersonPhone have a column called PhoneNumber?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

andy.julson Old Hand Points: 398 More actions · Answer 4

Yes, the columns in the dbo_PersonPhone table are -

PhoneID

TelephoneTypeID

PhoneNumber

dbo_HomePhone has many columns with the PK being PhoneID.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Can you post the CREATE TABLE statements for the following please?

dbo_HomePhone hp,

dbo_Address_G1 ad,

dbo_PhoneDelivery pd

dbo_PersonPhone pp

dbo_Subscription

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

andy.julson Old Hand Points: 398 More actions · Answer 6

I would like to thank everyone for their input on this matter but I have resolved the issue. I created a temp table called PHONE and selected the phone numbers separately and joined the subscriber table based off the phoneid. Thank you again!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Doesn't sound like the most efficient option. I'm sure this is a simple problem to resolve, if you can post the table definitions

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

andy.julson (12/29/2011)
...
FROM dbo_HomePhone hp, dbo_Subscription sub, dbo_Address_G1 ad, dbo_PhoneDelivery pd
LEFT OUTER JOIN dbo_PersonPhone pp
ON hp.PhoneID = pp.PhoneID
WHERE hp.PhoneID = pd.PhoneID and
pd.AddressID = ad.AddressID and
hp.PhoneID = sub.PhoneID and
pd.EndDate > GETDATE() and
pd.StartDate < GETDATE() and
sub.EndDate > GETDATE()and
sub.StartDate < GETDATE()
...

I didn't know that mixing ANSI and non-ANSI join syntax in the same statement was permitted. Why not change the lot to ANSI joins?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]