Where clause no give expected result

where

where clause not show expected result.

declare @PracticeId bigint =1000
select * from insurance i
WHERE ISNULL(I.DELETED,0)=0 
    and (
          (  @PracticeId =1000 and(I.PracticeId=@PracticeId and I.PracticeId<>0) ) 
        OR( (I.PracticeId=0 OR I.PracticeId=@PracticeId) and I.PracticeId<>1000 )
    )

I want show result when

@practiceid=1000

than only 1000 practices show and when

practiceid <>1000

than show practiceid in (0,@practiceid)

but when i pass @practiceid=1000 than its also get 0 practiceid

Best Answer

Your logic problem occurs in the way you are ORing i.PracticeId=0 into the criteria. That row is ORed so that it's always true. Effectively, you're using OR when you want it to act like ELSE (that's not how OR works).

I believe you want to add in this AND @PracticeId<>1000 to get the desired results:

declare @PracticeId bigint =1000
select * from insurance i
WHERE ISNULL(I.DELETED,0)=0 
    and (
          (  @PracticeId =1000 and(I.PracticeId=@PracticeId and I.PracticeId<>0) ) 
        OR( ((i.PracticeId=0 AND @PracticeId<>1000) OR I.PracticeId=@PracticeId) and I.PracticeId<>1000 )
    )

Note: this query will probably perform terribly and be very slow due to the way the criteria are ORed together. Solving this with dynamic SQL or an IF/ELSE block would likely simplify your logic and perform much better.

Related Solutions

Mysql – Query with WHERE NOT and NOT IN with subquery

Your unexpected results may be stemming from product_id being assigned to multiple categories in the oc_product_to_category table. If a product is in e.g. category 1 AND category 73, it will still be returned by the inner query and thus excluded from the outer query.

Produce a simple set of test data

INTO oc_product INSERT (product_id,  model, tax_class_id) VALUES
(1, 'model5', 10),
(2, 'AA', 5),
(3, '12', 8),
(4, '14', 12),
(5, 'xx', 12)

INTO oc_product_to_category INSERT (product_id, category_id) VALUES
(1, 1), -- product_id is in a category that is not in that list
(1, 73), -- product_id is STILL in a category that is not in that list; as well as a value in that list
(2, 73), -- product_id is only in categories in that list
(3, 17), -- product_id is only in categories not in that list
(4, 33), -- product_id is only in categories not on the category list, is excluded because of tax category
(5, 73), -- product_id is only in categories in the list, is excluded because of tax category

(I probably have the syntax of the INSERT query backwards;) )

I believe your current query will return

(2, 'AA', 5),

You probably want something like

SELECT 
    op.`product_id`,
    op.`model`,
    op.`tax_class_id` 
FROM
    oc_product AS op
  LEFT JOIN
    (SELECT DISTINCT p2c.product_id
     FROM oc_product_to_category as p2c
     WHERE
         p2c.category_id IN (73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)
    ) as OnlyProductsBelongingToTheseIncluded
  ON op.product_id = OnlyProductsBelongingToTheseIncluded.product_id
WHERE 
    ( op.tax_class_id <> 12 ) AND
    OnlyProductsBelongingToTheseIncluded.product_id IS NOT NULL

This should return

(1, 'model5', 10),
(2, 'AA', 5)

3 is excluded because it only exists in other groups; 4 and 5 are excluded because of the tax code

Changing the last line to AllProductsBelongingToTheseExcluded.product_id IS NULL (and the appropriate subquery alias) should return only the (3, '12', 8) datapoint

Sql-server – SQL Server — More efficient alternatives to repeating an (almost) identical subquery

Hard to say for sure without seeing the full schema and queries, to work on the execution plans. But you could try:

Where (Select Count(Distinct fld1) From t1 ...etc... where ...etc... and fld1 In ('X', 'Y', 'Z')) = 3

Best Answer

Related Solutions

Mysql – Query with WHERE NOT and NOT IN with subquery

Sql-server – SQL Server — More efficient alternatives to repeating an (almost) identical subquery

Related Question