This should do the trick. SELECT UMR.MachineResourceID, UMR.MachineResourceName, UMR.UniqueUserName, U.Mail0 FROM dbo.v_R_User U JOIN dbo.v_UserMachineRelationship UMR ON UMR.UniqueUserName = U.Unique_User_Name0 join (SELECT UMR.UniqueUserName as 'UniqueUserName' FROM dbo.v_UserMachineRelationship UMR group by UMR.UniqueUserName Having Count(*) >1) MT1 on MT1.UniqueUserName = UMR.UniqueUserName
From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On Behalf Of Enley, Carl Sent: Thursday, March 30, 2017 3:28 PM To: mssms@lists.myitforum.com Subject: [mssms] RE: Query for users with multiple primary devices You could use this and just filter excel to show the duplicates in the name field? SELECT UMR.MachineResourceID, UMR.MachineResourceName, UMR.UniqueUserName, Mail0 FROM v_R_User U JOIN v_UserMachineRelationship UMR ON UMR.UniqueUserName = U.Unique_User_Name0 I also believe there is a built in report where you can specify a collection and the number of UDA relationships > greater than 1 for example. From: listsad...@lists.myitforum.com<mailto:listsad...@lists.myitforum.com> [mailto:listsad...@lists.myitforum.com] On Behalf Of Beardsley, James Sent: Thursday, March 30, 2017 1:02 PM To: mssms@lists.myitforum.com<mailto:mssms@lists.myitforum.com> Subject: [mssms] Query for users with multiple primary devices Anyone have a SQL query that'll show me all users with more than one primary device assigned to them through UDA? Thanks, James ________________________________ Confidentiality Notice: This e-mail is intended only for the addressee named above. It contains information that is privileged, confidential or otherwise protected from use and disclosure. If you are not the intended recipient, you are hereby notified that any review, disclosure, copying, or dissemination of this transmission, or taking of any action in reliance on its contents, or other use is strictly prohibited. If you have received this transmission in error, please reply to the sender listed above immediately and permanently delete this message from your inbox. Thank you for your cooperation.