Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-17
5
Medium Priority
?
300 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 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