sql server - Work with multiple where statements with different conditions - Stack Overflow

admin管理员组

文章数量:1432566

I have the below SQL logic made up of three where statements each seperated by "OR". My goal is to retrieve all data within the first where statement, but exclude only those records that meet the criteria in the second two where statements. When I run it as is I get all the records. I think it's because of the first where statement, but I can't figure out how to change it so it only excludes the records that meet the criteria in the last two where statements. Any help you can provide to rewrite this logic is greatly appreciated.

WHERE 
(
    (ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN')
OR
    (o.ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'
    AND o.ord_revtype4 IN ('GRIFF3')
    AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY'))
OR
    (o.ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'
    AND o.ord_revtype4 IN ('GRIFF1')
    AND s.cmp_id NOT IN ('CATGRI02','CATWAC01'))
)

I have the below SQL logic made up of three where statements each seperated by "OR". My goal is to retrieve all data within the first where statement, but exclude only those records that meet the criteria in the second two where statements. When I run it as is I get all the records. I think it's because of the first where statement, but I can't figure out how to change it so it only excludes the records that meet the criteria in the last two where statements. Any help you can provide to rewrite this logic is greatly appreciated.

WHERE 
(
    (ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN')
OR
    (o.ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'
    AND o.ord_revtype4 IN ('GRIFF3')
    AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY'))
OR
    (o.ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'
    AND o.ord_revtype4 IN ('GRIFF1')
    AND s.cmp_id NOT IN ('CATGRI02','CATWAC01'))
)

• sql-server
• t-sql
• logic

Share Improve this question Follow edited Nov 18, 2024 at 21:43 Dale K 27.6k1515 gold badges5858 silver badges8383 bronze badges asked Nov 18, 2024 at 20:29 ChrisChris 322 bronze badges 17

• Could this perhaps help you? sql query with multiple where statements – Aixi Commented Nov 18, 2024 at 20:44
• 1 Please post sample data and desired results. – Barmar Commented Nov 18, 2024 at 20:52
• 1 @Barmar - not for inclusive start date and exclusive end date. The >= and < date range check is best practice. – T N Commented Nov 18, 2024 at 20:55
• 1 Please provide a minimal reproducible example with sample data and desired results. – Dale K Commented Nov 18, 2024 at 20:56
• 2 (repost) Do you perhaps need WHERE (conditions 1) AND NOT (conditions 2) AND NOT (conditions 3) or equivalently WHERE (conditions 1) AND NOT (conditions 2 OR conditions 3)? If so, you can also trim the second and third conditions down to just check the o.ord_revtype4 and s.cmp_id values. – T N Commented Nov 18, 2024 at 20:58

 |  Show 12 more comments

2 Answers 2

Sorted by: Reset to default 2

To "retrieve all data within the first [condition], but exclude ... those records that meet the criteria in the second two [conditions]", try:

WHERE (
    ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'
)
AND NOT (
    o.ord_revtype4 IN ('GRIFF3')
    AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY')
)
AND NOT (
    o.ord_revtype4 IN ('GRIFF1')
    AND s.cmp_id NOT IN ('CATGRI02','CATWAC01')
)

(Redundant conditions have been trimmed.)

Or equivalently:

WHERE (
    ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'
)
AND NOT (
    (
        o.ord_revtype4 IN ('GRIFF3')
        AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY')
    )
    OR (
        o.ord_revtype4 IN ('GRIFF1')
        AND s.cmp_id NOT IN ('CATGRI02','CATWAC01')
    )
)

This method is not the most efficient, but I often prefer it when excluding data. It allows you to identify exactly which records are being excluded by simply running the subquery alone. You can then check that it's excluding what you want it to, and not excluding things you don't;

SELECT * FROM [TABLE]

WHERE ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'

AND [PRIMARYKEY] NOT IN (SELECT [PRIMARYKEY] FROM [TABLE]
                        WHERE o.ord_billto = 'CATGRI'
                        AND o.ord_status = 'CMP'
                        AND o.ord_invoicestatus = 'XIN'
                        AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
                        AND (

                            (o.ord_revtype4 IN ('GRIFF3')
                            AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY'))
                        OR
                            (o.ord_revtype4 IN ('GRIFF1')
                            AND s.cmp_id NOT IN ('CATGRI02','CATWAC01'))
                            )
                        )

本文标签： sql serverWork with multiple where statements with different conditionsStack Overflow