StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Okay, you're attempting to find all <em>users</em> who share the same name/nickname.<br> I believe the following should work; </p> <pre><code>SELECT a.ID, a.LastName, a.FirstName FROM dbo.vw_Users_Details as a WHERE a.LastName <> '' AND a.FirstName <> '' AND EXISTS (SELECT '1' FROM dbo.vw_Users_Details as b LEFT JOIN pdNicknames as c ON (c.name = b.FirstName AND c.variation = a.FirstName) OR (c.name = a.FirstName AND c.variation = b.FirstName) WHERE b.ID <> a.ID AND b.LastName = a.LastName AND (b.FirstName = a.FirstName OR (c.name IS NOT NULL OR c.variation IS NOT NULL) ) ) </code></pre> <p>I make no garuantees about the execution performance of this statement, as you haven't provided enough information for us to know. However, it's likely to be better, given you won't need the OLAP; I do recommend indicies on the various names and <code>variation</code>, of course. I left off <code>Not_Dupe_Flag</code> because I'm a little confused by it's use (because you seem to be using '1' as 'false', which is <em>opposite</em> to how most comparisons are setup); at minimum, never include 'Not' as part of a boolean variable name - it makes reasoning about it difficult (use <code>Unique_Name</code> or <code>Duplicated_Name</code>, either of which is immediately understandable).</p> <hr/> <p>EDIT: </p> <p>If you need to restrict your selection, I recommend encapsulating the query in a view (including the <code>ROW_NUMBER()</code> function), and query the view. Alternatively, if your RDBMS supports it, wrap the query in a CTE. Multiple nested <code>FROM</code> clauses are like multiple nested <code>if</code> statements - confusing. Being able to logically seperate parts of the query with a view or CTE goes a long way to retaining sanity.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload