Solved

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

Posted on 2013-01-17
5
278 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL View nearest date 5 36
optimize stored procedure 6 25
Alternative of IN Clause in SQL Server 3 20
SQL Query assistance 16 24
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard 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.
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 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