{"id":11803,"date":"2022-07-13T08:17:58","date_gmt":"2022-07-13T07:17:58","guid":{"rendered":"https:\/\/sdna.lu\/?p=11803"},"modified":"2022-09-01T14:39:52","modified_gmt":"2022-09-01T13:39:52","slug":"query-select-person-that-should-be-match-in-blacklist","status":"publish","type":"post","link":"https:\/\/sdna.lu\/?p=11803","title":{"rendered":"Query: Select person that should be match in Blacklist"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><code>Select distinct [BlacklistedAlias].[FirstName] + ' ' + [BlacklistedAlias].[LastName]<br>From [BlacklistedAlias] Inner Join [Blacklisted] On [BlacklistedAlias].[BlacklistedId] = [Blacklisted].[Id]<br>Where [Blacklisted].[TableNumber] = 17 And ([BlacklistedAlias].[FirstName] + ' ' + [BlacklistedAlias].[LastName] in (<br>Select [Person].[FirstName] + ' ' + [Person].[LastName]<br>from [DeMaSy].[DeMaSy].[Person] as [Person] where [Person].[DBId] = XX) or [BlacklistedAlias].[LastName] + ' ' + [BlacklistedAlias].[FirstName] in (<br>Select [Person].[LastName] + ' ' + [Person].[FirstName]<br>from [DeMaSy].[DeMaSy].[Person] as [Person]<br>where [Person].[DBId] = XX )) Order by [BlacklistedAlias].[FirstName] + ' ' + [BlacklistedAlias].[LastName]<\/code><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Delete Record that should be match and where MatchPercentage is 0<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>Delete from Blacklist<br>where PersonId in<br>(select Person.Id<br>from DeMasy.Person as Person<br>Inner Join Licence.DeMaSy.Blacklisted as Blacklisted<br>On Person.LastName = Blacklisted.LastName and Person.FirstName = Blacklisted.FirstName and TableNumber = 17<br>where Person.DBId = XXX)<br>and Blacklist.DBId = XXX and Blacklist.MatchPercentage = 0<\/code><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>Delete from Blacklist<br>where CompanyId in<br>(select Company.Id<br>from Company<br>Inner Join Licence.DeMaSy.Blacklisted as Blacklisted<br>On Company.Name = Blacklisted.FirstName and TableNumber = 2<br>where Company.DBId = XXX)<br>and Blacklist.DBId = XXX and Blacklist.MatchPercentage = 0<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Select distinct [BlacklistedAlias].[FirstName] + &#8216; &#8216; + [BlacklistedAlias].[LastName]From [BlacklistedAlias] Inner Join [Blacklisted] On [BlacklistedAlias].[BlacklistedId] = [Blacklisted].[Id]Where [Blacklisted].[TableNumber] = 17 And ([BlacklistedAlias].[FirstName] + &#8216; &#8216; + [BlacklistedAlias].[LastName] in (Select [Person].[FirstName] + &#8216; &#8216; + [Person].[LastName]from [DeMaSy].[DeMaSy].[Person] as [Person] where [Person].[DBId] = XX) or [BlacklistedAlias].[LastName] + &#8216; &#8216; + [BlacklistedAlias].[FirstName] in (Select [Person].[LastName] + &#8216; &#8216; + [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[487],"tags":[],"class_list":["post-11803","post","type-post","status-publish","format-standard","hentry","category-query"],"_links":{"self":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11803","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11803"}],"version-history":[{"count":3,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11803\/revisions"}],"predecessor-version":[{"id":11808,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11803\/revisions\/11808"}],"wp:attachment":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11803"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11803"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11803"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}