Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'm not sure what you mean by one select statement, but I think what you really want to know is how to do this query so that it runs quickly, is easy to see it is correct and easy to maintain.</p> <p>Here is how to do that; use CTEs. (If you don't know what a CTE is go read about them and then come back here -- they are documented on Microsoft's website.)</p> <p>CTEs can be much faster and clearer than sub-queries. Let me show you how, taking the code above and re-factoring with CTEs I get:</p> <pre><code>WITH DocTypes AS ( SELECT VisitID, docType FROM VisitDocs ), DocType13 AS ( SELECT VisitID FROM DocTypes WHERE docType = 13 ), DocType1and2 AS ( SELECT VisitID FROM DocTypes WHERE docType IN (1,2) ), DocType1 AS ( SELECT VisitID FROM DocTypes1and2 WHERE docType = 1 ), DocType2 AS ( SELECT VisitID FROM DocTypes1and2 WHERE docType = 2 ), Base AS ( SELECT distinct v.VisitID, a.OfficeID AS OwningOfficeID, scp.OfficeID AS ScopeOfficeID, V.VisitDate, a.staffID as OwningStaff ,scp.StaffID as OwningScope FROM Visits v INNER JOIN VisitDocs vdoc ON vdoc.VisitID = v.VisitID INNER JOIN InspectionScope scp ON scp.ScopeID = v.ScopeID INNER JOIN Assignments a ON a.AssignmentID = scp.AssignmentID INNER JOIN Staff s ON s.StaffID = a.StaffID WHERE v.VisitType = 1 AND --'SCOPE OWNER AND LOOK FOR INSPECTION REPORT BUT NOT FOR COORD/FINAL REPORT. (scp.StaffID = 141 AND EXISTS(SELECT * FROM DocType13 d WHERE d.VisitID = v.VisitID) AND NOT EXISTS(SELECT * FROM DocType1and2 d WHERE d.VisitID = v.VisitID) ) OR --'ASSIGNMENT OWNER AND NOT SCOPE OWNER AND LOOK FOR COORDINATOR REPORT. (a.StaffID = 141 AND scp.StaffID != 141 AND EXISTS(SELECT * FROM DocType2 d WHERE d.VisitID = v.VisitID) AND NOT EXISTS(SELECT * FROM DocType1 d WHERE d.VisitID = v.VisitID) ) ) SELECT * FROM Base WHERE (OwningOfficeID &lt;&gt; ScopeOfficeID AND VisitDate &gt;='01/11/2012'( OR OwningOfficeID = ScopeOfficeID </code></pre> <p>It should be clear why this is better, but if it isn't briefly:</p> <ul> <li><p>All the selects you see at the beginning are exactly as they were in the original query, but because they are broken out prior the optimizer has an easier time, it will do better than when they were sub-queries -- I've seen huge changes in some cases, but in any case the optimizer has the option now to do a better job of memory caching and such.</p></li> <li><p>It is clearer and easier to test. If you have problems with the query you can test it easy, comment out the main select and just select one of the the sub-queries, is it what you expect? These sub-queries are simple, but they can get complicated and this makes it easier.</p></li> <li><p>You said you wanted to apply some logic to this query. I've added it to the CTE, as you can see it makes this complicated "layering" simple. </p></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POFiltering rows if two columns values are not same - sql server
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USHogan
    1. USHogan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThanks for showing CTEs method. I have tried that and can't see much difference in the performance.
      singulars
      1. PO
      1. USuser1263981
    2. CO@user1263981 - performance has many factors - size of tables, indexes, etc - you don't always see magic -- but you do see how to add your additional conditions to this query now?
      singulars
      1. PO
      1. USHogan
    3. COdon't know how to fit (OwningOfficeID <> ScopeOfficeID AND VisitDate >='01/11/2012' OR OwningOfficeID = ScopeOfficeID) with cte
      singulars
      1. PO
      1. USuser1263981
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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