StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>No you can not, you are using <code>D</code>, <code>E</code>, and <code>F</code> in your result set so you must join those tables. You could only remove them if you removed things like <code>D.FullName AS CallOpenedBy</code> and <code>E.FullName AS<br> EngineerName</code> and <code>F.FullName AS CallClosedBy</code> from your result.</p> <hr> <p>As a side note (and the I bet the original reason you asked this question), but if a call has not been closed yet and <code>A.CallClosedBy</code> has not been assigned to a value in <code>F.UserProID</code> the row will be excluded from the result. </p> <p>You should be using <code>LEFT OUTER JOIN</code> for those 3 tables if any of the 3 columns they link on may not have a corresponding record in the destination table or could be null.</p> <p>Here is how I would do it</p> <pre><code>Select B.BranchName, C.Name AS CustomerName,IsNull(D.FullName, '(Not Assigned)') AS CallOpenedBy, A.CallOpenTime,A.CallMode, A.CallType,A.ReportedBy, A.ReportedVia, A.ReportedProblem, A.MaterialsReceived, A.MisComments,A.CallPriority,A.EstimatedAmount,IsNull(E.FullName, '(Not Assigned)') AS EngineerName, A.ActionTaken, A.CallClosedTime,IsNull(F.FullName, '(Not Assigned)') AS CallClosedBy, A.Status, A.Amount From TblServiceCalls A INNER JOIN TblBranchInfo B ON A.BranchID = B.BranchID INNER JOIN TblMainMaster C ON A.MasterID = C.MasterID LEFT OUTER JOIN TblUserProfiles D ON A.CallOpenedBy = D.UserProID LEFT OUTER JOIN TblUserProfiles E ON A.Engineer = E.UserProID LEFT OUTER JOIN TblUserProfiles F ON A.CallClosedBy = F.UserProID </code></pre> <p>Now if the value is <code>NULL</code> or the record does not exist in the destination table it will say <code>(Not Assigned)</code> in the output instead of excluding the row.</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