How to list multiple combinated rows from one table

Question

I'm looking for a select query for the problem described below. This is a follow-up to the issue described in: How to select multiple records from a table with one field?

In this case however, we added some new columns to the table. The data is now like this:

ID	Warehouse_ID	Attribute_ID	Value
1	W01	101	Red
2	W01	201	XXL
3	W25	101	Blue
3	W25	201	M

My question is: how to list those warehouses, that have both Attribute_Id 101 with value RED and attribute_id 201 with value XXL. In the example above, we don't want to list W25, only W01.

In this case, should be there more conditions under where? maybe some select from where [..]? Could you help? I'd also prefer to avoid self joins, since the query will be most likely expanded in the future even more.

Kind regards, Piotr

Answer 1

This is a hack, but could work:

select warehouse_id 
from test
where ( attribute_id = 101 and value = 'Red') or 
      ( attribute_id = 201 and value = 'XXL')
group by warehouse_id
having count(*) = 2

Change the count to match the number of OR clauses you would have. Have you thought about storing one row per warehouse and store the attributes as a JSON column?

Answer 2

Here is a solution that will work for the following special cases as well (compared to the other answer posted already, by OldProgrammer). The first special case arises when a warehouse may appear with one of the required pairs exactly twice, while the other pair is not present. The example in my sample data is warehouse W05, the last two rows in the sample data. The other special case (not included in my sample data) is when both pairs are present for a warehouse, but at least of them appears two or more times. (Then the count is 3 or more instead of 2, if we don't count distinct pairs.)

Unlike your earlier question, where the solution was simple - just add a distinct directive to the count - in this case we need a trick, since Oracle SQL does not support counting distinct tuples. Why that is, only Oracle (and perhaps the maintainers of the SQL Standard) knows; relational databases are supposed to work with tuples as the most basic building block.

So - to work around this limitation, we need to "count distinct" something that reflects the tuples, but is a single expression. It would be easy to simply concatenate the two columns, but that may lead to wrong results in some cases; indeed, 'ab' concatenated to 'cde' is the same as 'abc' concatenated to 'de' even though the tuples are different.

The standard solution to that is to concatenate a character between the columns, a character that is not likely to appear in either column. The best choice for that is one of the ASCII control characters. Historically character 35, GS ("group separator"), is used as the field separator within records. (There are also "unit separator", "record separator" etc. among the ASCII control characters).

The solution then looks like this:

select warehouse_id
from   wh_at
where  (attribute_id, value) in ((101, 'Red'), (201, 'XXL'))
group  by warehouse_id
having count(distinct attribute_id || chr(35) || value) = 2
;

This produces the output

WAREHOUSE_ID
------------
W01

from the input data (for testing):

create table wh_at (id, warehouse_id, attribute_id, value) as
  select  1, 'W01', 101, 'Red'  from dual union all
  select  2, 'W01', 201, 'XXL'  from dual union all
  select  3, 'W25', 101, 'Blue' from dual union all
  select  4, 'W25', 201, 'M'    from dual union all
  select  5, 'W02', 106, 'XL'   from dual union all
  select  6, 'W02', 209, null   from dual union all
  select  7, 'W03', 156, 'Red'  from dual union all
  select  8, 'W03', 201, 'XXL'  from dual union all
  select  9, 'W04', 101, 'Red'  from dual union all
  select 10, 'W04', 201, 'S'    from dual union all
  select 11, 'W04', 303, 'M'    from dual union all
  select 12, 'W05', 101, 'Red'  from dual union all
  select 13, 'W05', 101, 'Red'  from dual
;