CODEWITHSUNDEEP
sqldb2where-clause
user2523167
user2523167

Reputation: 567

Is there a way to get result from sets of conditions in where clauses in DB2

I have the usual table of shirt inventory: Size, colour and brand. And I have to find matching prices and add on to a list in a spreadsheet

I am interested in [large, red, Nike],[small, white, Puma] and [medium, blue, H&M]

What I am doing right now is:

select size,colour,brand,price from inventory 
where size in ('large','small','medium')
and colour in ('red','white','blue')
and brand in ('Nike','Puma','H&M')

Then I get a long list of my inventory that meets ANY combination of the 3*3 conditions. I´ll then do a xlookup to find the three exact combinations I need.

(My actual list is obvious much much longer and with many more variables that has a lot of more values. But this is the simplest example)

This is clearly very ineffective but I do not know how to make my query more direct.

Is there some way to make a simple loop that loops through, say, an array of conditions? Something like

condA=('large','small','medium')
condB= ('red','white','blue')
condC=('Nike','Puma','H&M')
for a = 0 to 2
select size, colour,brand,price from inventory 
where size=condA(a)
and colour=condB(a)
and brand=condC(a)
next a

I am using a DB2 database if that makes a difference...

Upvotes: 0

Views: 158

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

Just assign a "group number" to every attribute and use such a group number in WHERE clause in addition. The advantage is, that you don't have to specify a long list of OR'ed predicates. It's enough just to create 3 tables-references for each attribute type, if you wish.

In the example below the combination:
[large, red, Nike] gets group number 1
[small, white, Puma] gets group number 2
[medium, blue, H&M] gets group number 3

select 
  inventory.size
, inventory.colour
, inventory.brand
, inventory.price 
from 
(
values 
  ('large', 'red', 'Nike', 1)
, ('small', 'white', 'Puma', 1)
, ('medium', 'blue', 'H&M', 1)
-- any other combinations like below are not returned
, ('large', 'red', 'Puma', 1)
, ('small', 'white', 'Nike', 1)
) inventory (size, colour, brand, price)
join
(
values ('large', 1), ('small', 2), ('medium', 3)
) sizes (size, id) on sizes.size = inventory.size
join
(
values ('red', 1), ('white', 2), ('blue', 3)
) colours (colour, id) on colours.colour = inventory.colour
join
(
values ('Nike', 1), ('Puma', 2), ('H&M', 3)
) brands (brand, id) on brands.brand = inventory.brand
where sizes.id = colours.id and colours.id = brands.id;

Upvotes: 0

Julissa DC
Julissa DC

Reputation: 259

The best approach would be to filter directly cases with the exact combinations you are looking for:

select size,colour,brand,price from inventory 
where (size ='large' and brand = 'Nike' and colour='red')
or ( size ='small' and brand = 'Puma' and colour='white') 
or (size ='medium' and brand = 'H&M' and colour='blue')

That way you don't need to look into the extracted results to make sure the combinations are correct

Upvotes: 1

Related Questions