Results 1 to 2 of 2

Thread: Embeded Selects

  1. #1
    Join Date
    Feb 2014
    Posts
    1

    Unanswered: Embeded Selects

    I have 2 tables... Case and Names.

    I want to select all fields in Names, count the number of cases an employee id shows up in the names table as long as the case status > 2, and have it display in 1 table, for the current case.

    Currently, I create a temporary table containing the count of employee ID in active cases... then join that to my main query for all fields in Names...

    I want to try and eliminate the creation of a temporary table. and just have it do the count as it builds the query... Is this possible?

    Here are the two queries separately...

    Temp Count Query - Creates the temp table "tbleIDCount"
    SELECT tblNames.eID, Count(tblNames.NamesID) AS CountOfNamesID
    FROM tblNames INNER JOIN tblCase ON tblNames.[CaseID] = tblCase.[CaseID]
    WHERE (((tblCase.CurrentStage)>2))
    GROUP BY tblNames.eID;

    Second Query
    SELECT tblNames.NamesID, tblNames.CaseID, tblNames.Priority, tblNames.Name, tblNames.EmpStatus, tblNames.Phone, tblNames.Job_Title, tblNames.eID, tblNames.Rcvd_Nothing, tblNames.Rcvd_Drives, tblNames.Rcvd_Systems, tblNames.Date_Survey_Received, tblNames.No_Survey_Sent, tblNames.Supplemental_Survey, tblNames.Second_Survey, tblNames.Third_Survey, tblNames.Email, tblNames.Term_Date, tblNames.Data_Location, tblNames.CollectingEnCase, tblNames.FullImage, tbleIDCount.CountOfNamesID
    FROM tblNames INNER JOIN tbleIDCount ON tblNames.eID = tbleIDCount.eID;


    Is this possible, or just a pipe dream?

    Thanks
    Vlad

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This ought to do the same things as your temp table version. As long as you index the tblCase.CaseID column, it ought to perform well too.

    Code:
    SELECT tblNames.NamesID, tblNames.CaseID, tblNames.Priority
    ,  tblNames.Name, tblNames.EmpStatus, tblNames.Phone
    ,  tblNames.Job_Title, tblNames.eID, tblNames.Rcvd_Nothing
    ,  tblNames.Rcvd_Drives, tblNames.Rcvd_Systems, tblNames.Date_Survey_Received
    ,  tblNames.No_Survey_Sent, tblNames.Supplemental_Survey, tblNames.Second_Survey
    ,  tblNames.Third_Survey, tblNames.Email, tblNames.Term_Date
    ,  tblNames.Data_Location, tblNames.CollectingEnCase, tblNames.FullImage
    ,  (SELECT Count(tblNames.NamesID)
          FROM tblCase
          WHERE  tblNames.[CaseID] = tblCase.[CaseID]
             AND 2 < tblCase.CurrentStage
       ) AS CountOfNamesID
       FROM tblNames;
    Just an observation, but prefix notation (prefixing every table name with tbl) is frowned on in many circles... It makes the code harder to read, and gets really confusing when you change a table into a view, CTE, or a function (if your SQL implementation supports those).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •