and / or condition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

and / or condition

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-09-28 : 07:13:35

hi,

i'm working on a query and have discovered something fairly simple regarding "and" / "or" condition.

if I use e.g.

id_product in ('1111','2222')

as a result i should get all products that match id = 1111 and id = 2222.

But if I do it like

id_product in ('1111')
and id_product in ('2222')

as a result i get 0 rows returned, where as i want to find invoices that have both products, and not those which have either product 1111 or 2222 or even both :)

thank you for any suggestions!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 07:22:22

Well, a normalized table for invoices can't have BOTH 1111 and 2222 in same record.

-- Both products
SELECT		Number
FROM		Invoices
GROUP BY	Number
HAVING		MAX(CASE WHEN ID_Product = '1111' THEN 1 ELSE 0 END) = 1
		AND MAX(CASE WHEN ID_Product = '2222' THEN 1 ELSE 0 END)  = 1

E 12°55'05.25"
N 56°04'39.16"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-09-28 : 07:38:01

peso, thank you.

i understand, but the problem is, if i want to select all invoices that have both products on the same invoice and not one of either products.

how should i solve the problem then?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 07:56:56

The query above returns all order numbers where at least both products 1111 and 2222 are involved.

If you want orders where only products 1111 and 2222 are involved, please use following query.

SELECT		Number
FROM		Invoices
GROUP BY	Number
HAVING		COUNT(DISTINCT ID_Product) = 2
		AND MIN(ID_Product) = '1111'
		AND MAX(ID_Product) = '2222'

Otherwise, please post proper and accurate sample data describing the problem together with your expected output.
Also provide detailed information about your business rules in this case.

E 12°55'05.25"
N 56°04'39.16"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-09-28 : 08:05:24

Peso, works fine!

i used wrong table field, therefore didn't get proper results! :)

sorry again!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 08:21:16

quote:
Originally posted by Peso

The query above returns all order numbers where at least both products 1111 and 2222 are involved.

If you want orders where only products 1111 and 2222 are involved, please use following query.
SELECT Number
FROM Invoices
GROUP BY Number
HAVING COUNT(DISTINCT ID_Product) = 2
AND MIN(ID_Product) = '1111'
AND MAX(ID_Product) = '2222'

Otherwise, please post proper and accurate sample data describing the problem together with your expected output.
Also provide detailed information about your business rules in this case.

E 12°55'05.25"
N 56°04'39.16"

Will this be more effecient?

SELECT		Number
FROM		Invoices
WHERE           ID_Product in ('1111','2222') 
GROUP BY	Number
HAVING		COUNT(DISTINCT ID_Product) = 2

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 08:48:09

It will get wrong result.
It filter for 1111 and 2222 only, and then count distinct over the only remaining 1111 and 2222
which always give a distinct result of 2 (if both 1111 and 2222 is found, of course).

No matter if there are other records (for example id_product 3333) assigned to same order.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 08:55:26

This however will work (but with worse performance)

SELECT	DISTINCT
	i.Number
FROM	Invoice AS i
WHERE	NOT EXISTS (SELECT * FROM Invoice AS u WHERE u.ID_Product NOT IN (1, 2) AND u.Number = i.Number)

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-28 : 08:58:53

quote:
Originally posted by Peso

It will get wrong result.
It filter for 1111 and 2222 only, and then count distinct over the only remaining 1111 and 2222
which always give a distinct result of 2 (if both 1111 and 2222 is found, of course).

No matter if there are other records (for example id_product 3333) assigned to same order.

E 12�05.25"
N 56�39.16"

the results of that aren't necessarily wrong; it depends on what you need, it wasn't really specified. Either interpretation may be correct. The question is: do we want rows that have ONLY 1111 and 2222 and NOTHING ELSE AT ALL returned, or do we want rows that contain BOTH 1111 and 2222 and may/may not have other values.

Also, without a primary key specified, it is hard to know what should happen if they have 1111 repeated one or more times, and also it is hard to optimize the proc; the distinct check may not be necessary depending on the constraints.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 09:10:49

In that case, if OP want BOTH 1111 and 2222 (at least) together with eventually other ID_Product
Madhi's suggestion is far more efficient than my first.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources