SQL Server Performance

Eliminate a sub query result set from the main query result set

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by beav1013, Apr 23, 2008.

  1. beav1013 New Member

    Hello. I have a main query joining to multiple subqueries. One of the subqueries' result set however needs to be eliminated from the overall result set. How is that written out? I'll just show the main query joined to the subquery in question. I currently have:SELECT
    dwt.work_atty as ID,work_atty_last_name
    as LastName,work_atty_first_name
    as FirstName,class_year
    as ClassYear,hire_date
    as HireDate,term_date
    as TermDate,
    DateDiff(month, hire_date, IsNull(term_date, GetDate())) as MonthsWorked,q
    .MonthsBilled,Active
    ,dwt
    .work_atty_title as Title,work_atty_sect_desc
    as PracticeGroup,work_atty_loc
    as Location,
    '' as 'GoodFit: Yes',
    '' as 'GoodFit: No',
    '' as 'GoodFit: Too Close to Call',
    '' as 'GoodFit: Not Enough Information'FROM
    DW_TIMEKEEP dwt
    -- This subquery shows which work_atty values were made partner before 200201. Any results should be excluded from the overall result set.LEFT
    OUTER JOIN(SELECT work_atty, FirstPeriod, work_atty_title
    FROM(SELECT dwht.work_atty, min(period) as FirstPeriod, dwht.work_atty_title
    FROM DW_HISTORIC_TIMEKEEP dwhtINNER JOIN DW_TIMEKEEP dwt on dwht.work_atty = dwt.work_atty
    WHERE hire_date >= '1/1/1996'
    AND dwht.work_atty_title LIKE '%partner%'GROUP BY dwht.work_atty, dwht.work_atty_title) as w
    WHERE FirstPeriod < 200501) as x on dwt.work_atty = x.work_atty
    The overall result set is 1088 records. The subquery result set is 55 records so if the join works properly, I should end up with 1033 results. Right?
    Any help would be appreciated. Thanks
  2. jagblue New Member

    HI
    BY defination
    If you want to see all the records of one input Table independent of whether there is a matching record in the other table or not. then its an outer join.
    Look at this link which will explain you all the Joins
    http://www.codinghorror.com/blog/archives/000976.html
    Thanks
  3. ranjitjain New Member

    Try adding the below statement in your query at the end and check the result.
    WHERE x.work_atty IS NULL
  4. beav1013 New Member

    i had tried that but it still was not working. i moved the left outer join to the end of the query and changed it to a where work_atty NOT IN subquery. That worked. Thanks for the help and advice.

Share This Page