admin管理员组

文章数量:1023795

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 edited Nov 18, 2024 at 21:43 Dale K 27.6k15 gold badges58 silver badges83 bronze badges asked Nov 18, 2024 at 20:29 ChrisChris 32 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

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'))
                            )
                        )

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 edited Nov 18, 2024 at 21:43 Dale K 27.6k15 gold badges58 silver badges83 bronze badges asked Nov 18, 2024 at 20:29 ChrisChris 32 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

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

版权声明：本文标题：sql server - Work with multiple where statements with different conditions - Stack Overflow 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/questions/1745596009a2158173.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。