Solved

Including Additional Records in Select that Don't Match Primary Critera

Posted on 2013-01-17
5
291 Views
Last Modified: 2013-01-18
I have a table from which I am selecting records based on a set of criteria. Another so many tables are left joined as part of the select and which contain additional data used by the criteria.

There is a field in the main table that contains a grouping ID which groups sets of records - all of the records in the group contain the same grouping ID but might not all satisfy the criteria.

I want to include in the select the records in any groups from which one or more records satisfy the criteria.

For example, if I have 5 records:

Record 1 satisfies the criteria and belongs to group A
Record 2 does not satisfy the criteria and belongs to group A
Record 3 satisfies the criteria and belongs to group B
Record 4 does not satisfy the criteria and belongs to group C
Record 5 satisfies the criteria and belongs to group D

I want the select to include records 1, 2, 3, and 5. Records 1, 3, and 5 are included because they satisfy the criteria. Record 2 is included because, even though it does not satisfy the criteria, it is part of group A and record 1 which does satisfy the criteria is also in group A.

Can I do this in one select statement? If not then what would be the most efficient way? Would this be made easier if the grouping ID was in a separate table?

Kevin
0
Comment
Question by:zorvek (Kevin Jones)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:appari
ID: 38790289
post your existing SQL.

you can try something like this,
;with CTE1 as (Select distinct GroupID from yourtables and joins where criteria)
Select columns from (same as in CTE1 tables and joins without criteria)
where GroupID in (Select GroupID from CTE1)
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 38790295
Here is the main select:

            SELECT
                        RM_Master.*,
                        LatestRMInventory.RMInventoryOnHand,
                        LatestRMInventory.RMInventoryAvailable,
                        LatestRMInventory.RMInventoryCommitted,
                        LatestRMInventory.RMInventoryOnOrder,
                        LatestRMInventory.RMInventoryReleased,
                        LatestRMInventory.RMNeedForBalanceOfSeason,
                        InventoryAverageData.AverageRMUsage,
                        InventoryAverageData.AverageRMBookings,
                        LatestRMProjections.Projection1,
                        LatestRMProjections.Projection2,
                        LatestRMProjectionChanges.ChangeDate,
                        ProjectionChangesSumData.ProjectionBefore,
                        ProjectionChangesSumData.ProjectionAfter
                  FROM RM_Master
                  LEFT JOIN @InventoryAverageData AS InventoryAverageData ON
                        InventoryAverageData.RMNumber = RM_Master.RMNumber
                  LEFT JOIN @LatestRMInventory AS LatestRMInventory ON
                        LatestRMInventory.RMNumber = RM_Master.RMNumber
                  LEFT JOIN @LatestRMProjections AS LatestRMProjections ON
                        LatestRMProjections.RMNumber = RM_Master.RMNumber
                  LEFT JOIN @LatestRMProjectionChanges AS LatestRMProjectionChanges ON
                        LatestRMProjectionChanges.RMNumber = RM_Master.RMNumber
                  LEFT JOIN @ProjectionChangesSumData AS ProjectionChangesSumData ON
                        ProjectionChangesSumData.RMNumber = RM_Master.RMNumber
                  LEFT JOIN RM_Commodities ON
                        RM_Commodities.RMNumberPart = LEFT(RM_Master.RMNumber, 2)
                  WHERE
                        (
                              @RMCommodity IS NULL
                                    OR
                              RM_Commodities.RMCommodity = @RMCommodity
                        )
                              AND
                        (
                              @FactoryLocation IS NULL
                                    OR
                              RM_Master.FactoryLocation = @FactoryLocation
                        )
                              AND
                        (
                              @RMCategory IS NULL
                                    OR
                              RM_Master.RMCategory = @RMCategory
                        )
                              AND
                        (
                              @RMColor IS NULL
                                    OR
                              RM_Master.RMColor = @RMColor
                        )
                              AND
                        (
                              @LeatherType IS NULL
                                    OR
                              RM_Master.LeatherType = @LeatherType
                        )
                              AND
                        (
                              @LeatherStatus IS NULL
                                    OR
                              RM_Master.LeatherStatus = @LeatherStatus
                        )
                              AND
                        (
                              @SupplierName IS NULL
                                    OR
                              RM_Master.SupplierName = @SupplierName
                        )

Kevin
0
 
LVL 39

Expert Comment

by:appari
ID: 38790304
what is the group ID column name?
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 38791990
RM_Master.RMGroupID

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 0 total points
ID: 38795529
I punted and did the deed using two selects and a temporary table:

      DECLARE @FilteredRMGroupKeys TABLE (
            RMGroupKey varchar(7))
      
      INSERT INTO @FilteredRMGroupKeys
            SELECT
                        DISTINCT RM_Master.RMGroupKey
                  FROM RM_Master
                  LEFT JOIN RM_Commodities ON
                        RM_Commodities.RMNumberPart = LEFT(RM_Master.RMNumber, 2)
                  WHERE
                        (
                              @RMCommodity IS NULL
                                    OR
                              RM_Commodities.RMCommodity = @RMCommodity
                        )
                              AND
                        (
                              @FactoryLocation IS NULL
                                    OR
                              RM_Master.FactoryLocation = @FactoryLocation
                        )
                              AND
                        (
                              @RMCategory IS NULL
                                    OR
                              RM_Master.RMCategory = @RMCategory
                        )
                              AND
                        (
                              @RMColor IS NULL
                                    OR
                              RM_Master.RMColor = @RMColor
                        )
                              AND
                        (
                              @LeatherType IS NULL
                                    OR
                              RM_Master.LeatherType = @LeatherType
                        )
                              AND
                        (
                              @LeatherStatus IS NULL
                                    OR
                              RM_Master.LeatherStatus = @LeatherStatus
                        )
                              AND
                        (
                              @SupplierName IS NULL
                                    OR
                              RM_Master.SupplierName = @SupplierName
                        )

      SELECT
                  RM_Master.*,
                  LatestRMInventory.RMInventoryOnHand,
                  LatestRMInventory.RMInventoryAvailable,
                  LatestRMInventory.RMInventoryCommitted,
                  LatestRMInventory.RMInventoryOnOrder,
                  LatestRMInventory.RMInventoryReleased,
                  LatestRMInventory.RMNeedForBalanceOfSeason,
                  InventoryAverageData.AverageRMUsage,
                  InventoryAverageData.AverageRMBookings,
                  LatestRMProjections.Projection1,
                  LatestRMProjections.Projection2,
                  LatestRMProjectionChanges.ChangeDate,
                  ProjectionChangesSumData.ProjectionBefore,
                  ProjectionChangesSumData.ProjectionAfter
            FROM RM_Master
            LEFT JOIN @InventoryAverageData AS InventoryAverageData ON
                  InventoryAverageData.RMNumber = RM_Master.RMNumber
            LEFT JOIN @LatestRMInventory AS LatestRMInventory ON
                  LatestRMInventory.RMNumber = RM_Master.RMNumber
            LEFT JOIN @LatestRMProjections AS LatestRMProjections ON
                  LatestRMProjections.RMNumber = RM_Master.RMNumber
            LEFT JOIN @LatestRMProjectionChanges AS LatestRMProjectionChanges ON
                  LatestRMProjectionChanges.RMNumber = RM_Master.RMNumber
            LEFT JOIN @ProjectionChangesSumData AS ProjectionChangesSumData ON
                  ProjectionChangesSumData.RMNumber = RM_Master.RMNumber
            LEFT JOIN RM_Commodities ON
                  RM_Commodities.RMNumberPart = LEFT(RM_Master.RMNumber, 2)
            LEFT JOIN @FilteredRMGroupKeys FilteredRMGroupKeys ON
                  FilteredRMGroupKeys.RMGroupKey = RM_Master.RMGroupKey
            WHERE
                  FilteredRMGroupKeys.RMGroupKey IS NOT NULL
            ORDER BY
                  RM_Master.RMGroupKey,
                  RM_Master.RMNumber

Kevin
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question