January 11, 2010 at 3:05 pm
ok this monster worked just fine until i added dbo.staff.firm. Did I miss something in there?
The error says line 1 and tells me specifically the field I added cannot be bound. it does exist. as a test I took everything before the first union and it works fine.
SELECT dbo.Staff.Firm, dbo.Person.FullName AS Name, dbo.Department.Description, dbo.Address.City, dbo.Address.StateCode AS State,
dbo.Company.Name AS Company
FROM dbo.Staff INNER JOIN
dbo.Person ON dbo.Staff.PersonID = dbo.Person.PersonID INNER JOIN
dbo.Department ON dbo.Staff.DepartmentID = dbo.Department.DepartmentID INNER JOIN
dbo.StaffAddress ON dbo.Staff.StaffID = dbo.StaffAddress.StaffID INNER JOIN
dbo.Address ON dbo.StaffAddress.AddressID = dbo.Address.AddressID INNER JOIN
dbo.Company ON dbo.Person.CompanyID = dbo.Company.CompanyID
UNION
SELECT dbo.Staff.Firm, Person_3.FullName AS Name, CAST('NONE' AS varchar(5)) AS Department, Address_2.City, Address_2.StateCode AS State,
Company_3.Name AS Company
FROM dbo.Staff AS Staff_3 INNER JOIN
dbo.Person AS Person_3 ON Staff_3.PersonID = Person_3.PersonID INNER JOIN
dbo.StaffAddress AS StaffAddress_4 ON Staff_3.StaffID = StaffAddress_4.StaffID INNER JOIN
dbo.Address AS Address_2 ON StaffAddress_4.AddressID = Address_2.AddressID INNER JOIN
dbo.Company AS Company_3 ON Person_3.CompanyID = Company_3.CompanyID
WHERE (Staff_3.DepartmentID IS NULL)
UNION
SELECT dbo.Staff.Firm, Person_2.FullName AS Name, Department_1.Description, CAST(' ' AS varchar(50)) AS City, CAST(' ' AS varchar(10)) AS State,
Company_2.Name AS Company
FROM dbo.Staff AS Staff_2 INNER JOIN
dbo.Person AS Person_2 ON Staff_2.PersonID = Person_2.PersonID INNER JOIN
dbo.Department AS Department_1 ON Staff_2.DepartmentID = Department_1.DepartmentID INNER JOIN
dbo.StaffAddress AS StaffAddress_3 ON Staff_2.StaffID = StaffAddress_3.StaffID INNER JOIN
dbo.Address AS Address_1 ON StaffAddress_3.AddressID = Address_1.AddressID INNER JOIN
dbo.Company AS Company_2 ON Person_2.CompanyID = Company_2.CompanyID
WHERE (Staff_2.StaffID NOT IN
(SELECT StaffID
FROM dbo.StaffAddress AS StaffAddress_2))
UNION
SELECT TOP (100) PERCENT dbo.Staff.Firm, Person_1.FullName AS Name, CAST('NONE' AS varchar(5)) AS Department, CAST(' ' AS varchar(50)) AS City,
CAST(' ' AS varchar(10)) AS State, Company_1.Name AS Company
FROM dbo.Staff AS Staff_1 INNER JOIN
dbo.Person AS Person_1 ON Staff_1.PersonID = Person_1.PersonID INNER JOIN
dbo.Company AS Company_1 ON Person_1.CompanyID = Company_1.CompanyID
WHERE (Staff_1.StaffID NOT IN
(SELECT StaffID
FROM dbo.StaffAddress AS StaffAddress_1)) AND (Staff_1.DepartmentID IS NULL)
January 11, 2010 at 3:42 pm
You need to use the alias name that you've given dbo.Staff in the UNIONed queries. You've aliased all but the first query as Staff_3, Staff_2, etc. You're SELECT should read
SELECT alias.Firm,
......
FROM ......
January 11, 2010 at 4:07 pm
UNION
SELECT
dbo.Staff.Firm, --dbo.Staff is aliased Staff_3, this should be Staff_3.Firm
Person_3.FullName AS Name, CAST('NONE' AS varchar(5)) AS Department, Address_2.City, Address_2.StateCode AS State,
Company_3.Name AS Company
FROM dbo.Staff AS Staff_3 INNER JOIN
dbo.Person AS Person_3 ON Staff_3.PersonID = Person_3.PersonID INNER JOIN
dbo.StaffAddress AS StaffAddress_4 ON Staff_3.StaffID = StaffAddress_4.StaffID INNER JOIN
dbo.Address AS Address_2 ON StaffAddress_4.AddressID = Address_2.AddressID INNER JOIN
dbo.Company AS Company_3 ON Person_3.CompanyID = Company_3.CompanyID
WHERE (Staff_3.DepartmentID IS NULL)
UNION
See my comment above. Similar changes should be made in the other unioned queries where you have aliased dbo.Staff.
January 12, 2010 at 7:00 am
DOH! It figures. It was a long day. I was thinking I was likely not looking at it right.:pinch:
January 12, 2010 at 7:02 am
And of course that fixed everything. :blush:
January 12, 2010 at 8:09 am
Looks like a trees and forest issue. Glad to be able to help.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply