LEFT JOIN dbo.RE7.CONSTIT_ADDRESS_PHONES AS dbo.RE7.CONSTIT_ADDRESS_PHONES1 
ON dbo.RE7.CONSTIT_ADDRESS.ADDRESS_ID = dbo.RE7.CONSTIT_ADDRESS_PHONES1.CONSTITADDRESSID

quote:

---

Originally posted by garp2100

Greeting everyone, here's a nutcracker for you guys to ponder...
I've been given a query whose purpose is to display data from tables withing two different databases. We are dealing with a rather complex third-party application, and to illustrate an example, in order to pull up very basic info from a user (name, address, state, country) we need to go through 5 different tables. The two databases are denoted by RE7 and CertifiedObservers, the query I've been given to troubleshoot returns and error message of:
"Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '.'."

the query goes as follows (sorry for it being rather large):

SELECT dbo.RE7.RECORDS.CONSTITUENT_ID, dbo.RE7.CONSTITUENT.KEY_NAME, dbo.RE7.CONSTITUENT.FIRST_NAME,
dbo.RE7.CONSTITUENT.MIDDLE_NAME,
dbo.RE7.member.MemID, dbo.RE7.membershipTransaction.ExpiresOn, dbo.RE7.ADDRESS.ADDRESS_BLOCK, dbo.RE7.ADDRESS.CITY,
dbo.RE7.ADDRESS.STATE,
dbo.RE7.ADDRESS.POST_CODE, dbo.RE7.TABLEENTRIES.LONGDESCRIPTION, dbo.RE7.PHONES.NUM, dbo.RE7.PHONES_1.NUM, dbo.CertifiedObservers.Obsvr_exp.tournament,
dbo.CertifiedObservers.Obsvr_exp.observe_date, dbo.CertifiedObservers.Obsvr_exp.blue, dbo.CertifiedObservers.Obsvr_exp.white, dbo.CertifiedObservers.Obsvr_exp.striped, dbo.CertifiedObservers.Obsvr_exp.black,
dbo.CertifiedObservers.Obsvr_exp.sail, dbo.CertifiedObservers.Obsvr_exp.spear,
dbo.CertifiedObservers.Obsvr_exp.sword, dbo.CertifiedObservers.Obsvr_exp.score, dbo.CertifiedObservers.Obsvr_exp.captain, dbo.CertifiedObservers.Obsvr_exp.phone, dbo.CertifiedObservers.Obsvr_exp.boatname, dbo.CertifiedObservers.Obsvr_exp.comments, dbo.CertifiedObservers.Obsvr_exp.tdcomments
FROM dbo.RE7.RECORDS RIGHT JOIN dbo.RE7.ADDRESS RIGHT JOIN dbo.RE7.CONSTIT_ADDRESS ON dbo.RE7.ADDRESS.ID = dbo.RE7.CONSTIT_ADDRESS.ADDRESS_ID
LEFT JOIN dbo.RE7.TABLEENTRIES ON dbo.RE7.ADDRESS.COUNTRY = dbo.RE7.TABLEENTRIES.TABLEENTRIESID
INNER JOIN dbo.RE7.member INNER JOIN dbo.RE7.CONSTITUENT ON dbo.RE7.member.ConstitID = dbo.RE7.CONSTITUENT.RECORDS_ID
ON dbo.RE7.CONSTIT_ADDRESS.CONSTIT_ID = dbo.RE7.CONSTITUENT.RECORDS_ID INNER JOIN dbo.RE7.membershipTransaction
ON dbo.member.ID = dbo.membershipTransaction.MembershipID ON dbo.RE7.RECORDS.ID = dbo.RE7.CONSTITUENT.RECORDS_ID
LEFT JOIN dbo.RE7.PHONES RIGHT JOIN dbo.RE7.CONSTIT_ADDRESS_PHONES
ON dbo.RE7.PHONES.PHONESID = dbo.RE7.CONSTIT_ADDRESS_PHONES.PHONESID ON dbo.RE7.CONSTIT_ADDRESS.ADDRESS_ID = dbo.RE7.CONSTIT_ADDRESS_PHONES.CONSTITADDRESSID
LEFT JOIN dbo.RE7.CONSTIT_ADDRESS_PHONES AS dbo.RE7.CONSTIT_ADDRESS_PHONES1 ON dbo.RE7.CONSTIT_ADDRESS.ADDRESS_ID = dbo.RE7.CONSTIT_ADDRESS_PHONES1.CONSTITADDRESSID
LEFT JOIN dbo.RE7.PHONES AS dbo.RE7.PHONES_1 ON dbo.RE7.CONSTIT_ADDRESS_PHONES_1.PHONESID = dbo.RE7.PHONES_1.PHONESID LEFT JOIN dbo.CertifiedObservers.Obsvr_exp ON dbo.RE7.RECORDS.CONSTITUENT_ID = dbo.CertifiedObservers.Obsvr_exp.RE_ConstituentID_Link
WHERE dbo.RE7.membershipTransaction.ExpiresOn = or >= or <= or <> GetDate AND dbo.RE7.membershipTransaction.Category=41 Or dbo.RE7.membershipTransaction.Category=42 Or dbo.RE7.membershipTransaction.Category=43 Or dbo.RE7.membershipTransaction.Category=44 AND dbo.RE7.CONSTIT_ADDRESS.TYPE=181 Or dbo.RE7.CONSTIT_ADDRESS.TYPE=1121
AND dbo.RE7.PHONES.PHONETYPEID=415 AND dbo.RE7.PHONES_1.PHONETYPEID=417 Or dbo.RE7.PHONES_1.PHONETYPEID=692
ORDER BY dbo.RE7.CONSTITUENT.KEY_NAME, dbo.RE7.CONSTITUENT.FIRST_NAME;

line 16 I've highlighted in red.
Thank you so much for your help!

---

WHERE		dbo.RE7.membershipTransaction.ExpiresOn <= GetDate()
		AND dbo.RE7.membershipTransaction.Category IN (41, 42, 43, 44)
		AND dbo.RE7.CONSTIT_ADDRESS.TYPE IN (181, 1121)
		AND dbo.RE7.PHONES.PHONETYPEID=415
		AND PHONES_1.PHONETYPEID IN (417, 692)