Picking out mismatched records statement

[esu4edp]

I have two tables: Property and Propman

They both have the following fields
PropertyID - Example 786
DeptNo - Example 01
IncdNo - Example 897
YearOf - Example 07

I'm trying to compare the two tables to find any records the have the same deptno, incdno and yearof but have different property id numbers!

Example:
Property 01-897-07(DeptNo, IncdNo,YearOf) 786(PropertyID)
PropMan 01-897-07(DeptNo, IncdNo,YearOf) 258(PropertyID)


Whats wrong with my statement?

Select Property.DeptNo,Property.YearOf,Property.IncdNo
from dbo.Property
INNER Join PropMan on PropMan.PropertyID=Property.PropertyID
and PropMan.DeptNo=Property.DeptNo
and PropMan.YearOf=Property.YearOf
and PropMan.IncdNo=Property.IncdNo
WHERE PropMan.PropertyID=Property.PropertyID

 

[gmmastros]

[!]different property id numbers![/!]

Your query should return those that have the same property id's. Try this one instead. Notice I removed the PropertyId join condition and changed the where clause to <>

Select Property.DeptNo,
       Property.YearOf,
       Property.IncdNo,
       Property.PropertyId As PropertyId,
       PropMan.PropertyId As PropManPropertyId
from   dbo.Property
       INNER Join PropMan 
         On  PropMan.DeptNo=Property.DeptNo
         and PropMan.YearOf=Property.YearOf
         and PropMan.IncdNo=Property.IncdNo
WHERE PropMan.PropertyID <> Property.PropertyID

-George

"the screen with the little boxes in the window." - Moron