Solved

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

Posted on 2013-01-17
5
271 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)
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:appari
Comment Utility
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)
Comment Utility
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
Comment Utility
what is the group ID column name?
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
Comment Utility
RM_Master.RMGroupID

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 0 total points
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now