If you don't need the other data from the NETWORK_STATUS table, how about:
select *
from dbo.NETWORK AS n
inner join dbo.vwNETWORK_KEYMSTN AS km
on n.Network_ID = km.Network_ID
inner join dbo.vwAPPROVAL_LATEST AS a
on n.Network_ID = a.Network_ID
inner join dbo.APPROVAL_VINTAGE AS av
on a.Approval_ID = av.Approval_ID
and km.Milestone_Type_ID = av.Milestone_Type_ID
inner join dbo.NETWORK_MILESTONE AS m
on A.Approval_ID = m.Approval_ID
and km.Milestone_Type_ID = m.Milestone_Type_ID
inner join dbo.REF_MILESTONE AS rm
on km.Milestone_Type_ID = rm.Milestone_Type_ID
WHERE EXISTS
(
SELECT 1 FROM dbo.NETWORK_STATUS
WHERE Network_ID = n.Network_ID
and Status_Type_ID = 2
--and Status_Type_ID = rm.Status_Type_ID
);
This will get your result set, though I did add the Name field for clarity. The common table expression (cte) with ROW_NUMBER() will get the email priority. Then I left joined that back to the base table to return the names and a NULL for Julius.
CREATE TABLE #emailOrPhone (ID INT IDENTITY(1,1), Type INT, Number VARCHAR(50), Name VARCHAR(20))
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1213','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1312','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1012,'sally@ourdomain.com','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'sallyf@gmail.com','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'frankiebaby@yahoo.com','Frank')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'franks@ibm.com','Frank')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1010,'555-123-9876','Julius')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'425-369-1470','Julius')
;WITH cte AS
(
SELECT Name, Number
, CASE WHEN Number like '%@ourdomain.com' THEN 1 ELSE 2 END AS 'emailPriority'
, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) as rn
FROM #emailOrPhone
WHERE Number like '%@%'
)
SELECT DISTINCT Names.Name, Emails.Number
FROM #emailOrPhone as Names
LEFT JOIN cte as Emails on Names.Name = Emails.Name and Emails.rn = 1
ORDER BY Names.Name, Emails.Number
DROP TABLE #emailOrPhone
Best Answer
I don't think that this (masking only certain rows in the table) can be done with Dynamic Data Masking.
You could create a view that will do what you want to do by doing something like this:
Note that this will not mask the data from the "email" table so if you want to make sure the users cannot access it, you will have to restrict the access.
Note also that this have some limitation. If you are expecting the view to return result for a query like :
it will not return any row.