StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I would go with an anti semi-join (<code>NOT EXISTS</code> statement):</p> <pre><code>SELECT AllS2.[Project], AllS2.[Contractor], AllS2.[Service] FROM [StackoverflowProjects] AllS2 WHERE AllS2.[Service] = 'S2' AND NOT EXISTS (SELECT NULL FROM StackoverflowProjects NonS2 WHERE AllS2.Project = NonS2.Project AND AllS2.Contractor = NonS2.Contractor AND AllS2.Service <> 'S2') </code></pre> <p>Let me know if you would like help with the explanation. It does, however, work.</p> <h2>Update</h2> <p>First let me point out, I renamed the aliases to be less abstract and hopefully more helpful.</p> <blockquote> <p>I want to find contractors that provided S2 on a project and did not provide any other service on the same project they provided S2.</p> </blockquote> <p>I knew from this statement that you wanted contractors that only provided S2 service. The <code>NOT</code> part can be a little tricky if you're not already familiar with the <code>EXISTS</code> part. </p> <p>So let me do the opposite and explain what it does:</p> <pre><code>SELECT AllS2.[Project], AllS2.[Contractor], AllS2.[Service] FROM [StackoverflowProjects] AllS2 WHERE AllS2.[Service] = 'S2' AND EXISTS (SELECT NULL FROM StackoverflowProjects NonS2 WHERE AllS2.Project = NonS2.Project AND AllS2.Contractor = NonS2.Contractor AND AllS2.Service <> 'S2') </code></pre> <p>The will show you all contractors' projects who performed S2 service as well as any service other than S2.</p> <p>Also, here's a <a href="http://bradsruminations.blogspot.com/search/label/Anti%20Semi%20JOIN" rel="nofollow">link to a blog article</a> that helped me understand the benefits of Semi Joins (<code>EXISTS</code>) and Anti-Semi Joins (<code>NOT EXISTS</code>) when I was first learning.</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