Hi guys I've got a situation like this:

There is a table WH_AT:

And what I want to search, is all warehouses (warehouse_ID) that has BOTH Attribute_ID 101 and 201 for example. Now I know that select * from WH_AT where Attribute_Id = 101 and Attribute = 201 won't work, but I tried self-joins and failed miserably (i tried:

select * 
from WH_AT w1 
join WH_AT w2 on w1.ID = w2.ID
where w1.Attribute_ID = 101 and w2.Attribute_ID = 201

unfortunately got some errors, and while specyfying w1.ID != w2.ID it returned basicaly the whole table.

How do i get Warehouse_IDs that has both Attributes: 101 and 201? And how to write a query as simple as possible, to be possibly most effective?

CodePudding user response：

Query: (no self-join needed regardless of how many attributes are required; what is required is writing them in the where clause, and counting them for the having clause)

select warehouse_id
from   wh_at
where  attribute_id in (101, 201)
group  by warehouse_id
having count(distinct attribute_id) = 2
;

With the sample data documented at the end, this produces the following output:

WAREHOUSE_ID
------------
W01
W04

The test data is shown below. I added a few more examples, to make sure the query works correctly for those cases. Especially the last example, where the same attribute is shown for the same warehouse more than once. (Perhaps this is not possible in your data due to a unique constraint, but if that is so, then you should mention it in your question.)

create table wh_at (id, warehouse_id, attribute_id) as
  select  1, 'W01', 101 from dual union all
  select  2, 'W01', 201 from dual union all
  select  3, 'W02', 106 from dual union all
  select  4, 'W02', 209 from dual union all
  select  5, 'W03', 156 from dual union all
  select  6, 'W03', 201 from dual union all
  select  7, 'W04', 101 from dual union all
  select  8, 'W04', 201 from dual union all
  select  9, 'W04', 303 from dual union all
  select 10, 'W05', 101 from dual union all
  select 11, 'W05', 101 from dual
;

CodePudding user response：

You are very close. Your query is almost correct.

You can join the table with itself. The first one to search for 101, the second one to search for 102. Then, a join can find the matching warehouses. For example:

select distinct a.warehouse_id
from wh_at a
join wh_at b on b.warehouse_id = a.warehouse_id
where a.attribute_id = 101 and b.attribute = 102