A few Joins - 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

General SQL Server Forums

New to SQL Server Programming

A few Joins

Author

Topic

biogem
Starting Member

13 Posts

Posted - 2009-08-25 : 15:57:06

I have the below query and it needs to join several tables, but I can't figure how to get the correlation names to work, Help.

SELECT DISTINCT A.CARLYLE.DBO.LEAS.OCCPNAME, A.CARLYLE.DBO.LEAS.MOCCPID, TENANTNOTES.EMPLOYEEID,
tblTenant_MOCCP.pfkTENANTID, A.CARLYLE.DBO.LEAS.OCCPSTAT, EMPLOYEE.DISPLAYNAME
FROM CARLYLE.DBO.LEAS A LEFT JOIN tblTenant_MOCCP
ON A.CARLYLE.DBO.LEAS.MOCCPID = tblTenant_MOCCP.pfkMOCCPID
LEFT JOIN TENANTNOTES ON tblTenant_MOCCP.PFKTENANTID = TENANTNOTES.TENANTID
RIGHT JOIN EMPLOYEE ON TENANTNOTES.EMPLOYEEID = EMPLOYEE.EMPLOYEEID
INNER JOIN CARLYLE.DBO.CMLEDG WITH (NOLOCK) ON CARLYLE.DBO.CMLEDG.BLDGID = CARLYLE.DBO.BLDG.BLDGID
INNER JOIN CARLYLE.DBO.LEAS WITH (NOLOCK) ON CARLYLE.DBO.CMLEDG.BLDGID = CARLYLE.DBO.LEAS.BLDGID
AND CARLYLE.DBO.CMLEDG.LEASID = CARLYLE.DBO.LEAS.LEASID
WHERE CARLYLE.DBO.LEAS.OCCPSTAT = 'C'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 16:02:21

[code]SELECT DISTINCT a.occpname,
a.moccpid,
tenantnotes.employeeid,
tbltenant_moccp.pfktenantid,
a.occpstat,
employee.displayname
FROM carlyle.dbo.leas a
LEFT JOIN tbltenant_moccp
ON a.moccpid = tbltenant_moccp.pfkmoccpid
LEFT JOIN tenantnotes
ON tbltenant_moccp.pfktenantid = tenantnotes.tenantid
RIGHT JOIN employee
ON tenantnotes.employeeid = employee.employeeid
INNER JOIN carlyle.dbo.cmledg WITH (NOLOCK)
ON carlyle.dbo.cmledg.bldgid = carlyle.dbo.bldg.bldgid
INNER JOIN carlyle.dbo.leas WITH (NOLOCK)
ON carlyle.dbo.cmledg.bldgid = carlyle.dbo.leas.bldgid
AND carlyle.dbo.cmledg.leasid = carlyle.dbo.leas.leasid
WHERE carlyle.dbo.leas.occpstat = 'C' [/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

biogem
Starting Member

13 Posts

Posted - 2009-08-25 : 16:13:40

I'm getting this error message now.

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "carlyle.dbo.bldg.bldgid" could not be bound.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 16:41:41

Yes because there is no table named carlyle.dbo.bldg in your from or join but in the on clause
ON carlyle.dbo.cmledg.bldgid = carlyle.dbo.bldg.bldgid
you're trying to use it.
I think it is a typo but I don't know your table structure.

No, you're never too old to Yak'n'Roll if you're too young to die.

biogem
Starting Member

13 Posts

Posted - 2009-08-25 : 16:55:08

Yes, you're correct I pasted too many times. It works after I deleted the excess.
Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 16:57:34

welcome