I have a SELECT command that I need to pull out user according to their contact status. The hierarchy is: PRIMARY, SECONDARY, SUBSTITUTE. In other words, if a RequestID + RouteOrder has three contacts each with a different status as listed previously, thenn only the one with PRIMARY will be selected. I am trying to use a UNION, but don't think my output to be distinctive enough while still getting the UserID back. Heres the code. Do you have a suggestions.

SELECT a.[RequestID] + a.[RouteOrder],
a.[ID],
a.[UserIDContact],
a.[IsRequired],
, c.[TypeIDContactStatus],
FROM ContactStatus_vw c,
AvailablePendingRoutePointUser_vw a
WHERE a.[TypeIDContactStatus] = c.[TypeIDContactStatus]
AND c.[Code] = 'PRIMARY'
UNION
SELECT a2.[ID],
a2.[RequestID],
a2.[UserIDContact],
a2.[IsRequired],
a2.[RouteOrder],
c2.[TypeIDContactStatus]
FROM ContactStatus_vw c2,
AvailablePendingRoutePointUser_vw a2
WHERE a2.[TypeIDContactStatus] = c2.[TypeIDContactStatus]
AND c2.[Code] = 'SECONDARY'
UNION
SELECT a3.[ID],
a3.[RequestID],
a3.[UserIDContact],
a3.[IsRequired],
a3.[RouteOrder],
c3.[TypeIDContactStatus]
FROM ContactStatus_vw c3,
AvailablePendingRoutePointUser_vw a3
WHERE a3.[TypeIDContactStatus] = c3.[TypeIDContactStatus]
AND c3.[Code] = 'SUBSTITUTE'