You can try to change second condition to

			AND t1.mpinumber > t2.mpinumber
--or
			AND t1.mpinumber < t2.mpinumber

whichever you prefer.


>Sergey
>
>As I reviewed the results, I noticed that I get each duplicate record twice,
>example:
>
>

>MedRec1    medrec2     PatientID1    PatientID2
>000105243  105243      1172214       1090258
>... more data
>105243     000105243   1090258       1172214
>

>
>Is there anyway to filter on distinct on this query?
>
>

>SELECT t1.mpinumber AS MedRecNo1, t2.mpinumber AS MedRecNo2,
>		t1.PatientID AS PatientID1, t2.PatientID AS PatientID2,
>		t1.Lastname as LastName1, t2.LastName as LastName2
>	FROM Patients t1
>		JOIN Patients t2 ON CAST(t1.mpinumber AS int) = CAST(t2.mpinumber AS int)
>			AND t1.mpinumber <> t2.mpinumber
>order by t1.mpinumber
>

>
>Thanks again