Select with multiple choices - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Select with multiple choices

Author

Topic

henrikf
Starting Member

7 Posts

Posted - 2006-02-28 : 12:11:50

Hello there!

I have another tricky Q. Here’s a sample of my data. Each order contains several order lines. One order line can only meet one of the criteria A, B and C, but can have all possible combinations.

OrderNo...OrderLine...A....B....C
1010......1...............1....0....0
1010......2...............0....1....0
1010......3...............0....0....1

1020......1...............1....0....0
1020......2...............1....0....0
1020......3...............1....0....0

1030......1...............0....0....1
1030......2...............0....0....1
1030......3...............0....0....1

I would like to use SELECT to find the unique customer order numbers if they meet the following criteria:

(A=1 AND B=1) OR
(A=1 AND C=1) OR
(A=1 AND B=1 OR C=1) OR
(B=1) OR
(B=1 AND C=1)

All cases are applicable within each order number.

The only two cases that are not meet is when all lines have A=1 or if they have C=1.

I’m not sure how to search within the customer order for the combinations. I guess I should use self join, but I'm not sure how to write it. Please help!

//Henrik

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-28 : 12:19:42

You near enough written it yourself I think..


select distinct OrderNo 
from OrderTable
where
(A=1 AND B=1) OR
(A=1 AND C=1) OR
(A=1 AND B=1) OR
(A=1 AND C=1) OR
(B=1) OR
(B=1 AND C=1)

henrikf
Starting Member

7 Posts

Posted - 2006-02-28 : 15:17:19

Hmmm, its close, very close. However, the case where (A=1 AND C=1) wont be picked up.

This is the kind of orders I miss in the Q.

OrderNo...OrderLine...A....B....C
1010......1...............1....0....0
1010......2...............0....0....0
1010......3...............0....0....1

Its strange I think (but of course I'm not a pro...)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources