DECLARE @View1 TABLE(ID INT)
DECLARE @View2 TABLE(ID INT)

INSERT @View1
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 5
UNION ALL SELECT 7
UNION ALL SELECT 8



INSERT @View2
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 9
UNION ALL SELECT 10

SELECT 
	COALESCE(V1.ID, V2.ID) AS ID
FROM 
	@View1 AS V1
FULL OUTER JOIN
	@View2 AS V2
	ON V1.ID = V2.ID
WHERE
	V1.ID IS NULL
	OR V2.ID IS NULL

quote:

---

Originally posted by ZMike

I'm having trouble with the following scenario.

I have View1 and view2.. I want to see the account #s that do not match from file 1 and file 2. It sounds like I wanted to do a left join since View1 should have every account in it that I'd need. But if there is no match in View2 then those are the accounts that I want to see. Here is what I have. I'm guessing that I need to use the Not Exist but I've tried that a few ways to no avail.

Any help would be greatly appriciated.



SELECT DISTINCT
Balancing_Part1.SiteID, Balancing_Part1.Account#, Balancing_Part1.Equipment_Status,
Balancing_Part1.Equip_Detail_Status, Balancing_Part1.Item#,
Balancing_Part1.Serial#, Balancing_Part1.Equipment_Type,
Balancing_Part1.Service_Category_Code,
Balancing_Part1.Service_Occurence,
Balancing_Part1.Disconnect_Date
FROM Balancing_Part1 LEFT JOIN
Balancing_Part2 ON Balancing_Part1.Account# = Balancing_Part2.Account_Number
AND Balancing_Part1.SiteID = Balancing_Part2.Site_ID
WHERE Balancing_Part1.Account# <> Balancing_Part2.Account_Number
Balancing_Part2.Account_Number IS NULL
ORDER BY Balancing_Part1.Account#

---