Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

564 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