0

Hi guys I've got a situation like this:

There is a table WH_AT:

ID Warehouse_ID Attribute_ID
1 W01 101
2 W01 201
3 W02 106
4 W02 209
5 W03 156
6 W03 201

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?

Piotr Nazim
  • 3
  • 2
  • Does this answer your question? [SQL query to find only those customer ids which have 2 source values](https://stackoverflow.com/questions/62213806/sql-query-to-find-only-those-customer-ids-which-have-2-source-values) – astentx Jun 23 '22 at 17:53
  • Or [SQL query: Simulating an "AND" over several rows instead of sub-querying](https://stackoverflow.com/questions/163887/sql-query-simulating-an-and-over-several-rows-instead-of-sub-querying) – astentx Jun 23 '22 at 18:00
  • Note that tables have _rows_ and _columns_, not records or fields. – jarlh Jun 23 '22 at 18:57

2 Answers2

0

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
The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • Thank you! I wasn't aware that using warehouse_id instead of just the primary key makes a difference, but it kinda makes sense now. not so logical for me still, but I'll remember :) – Piotr Nazim Jun 23 '22 at 20:36
0

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
;
  • That's great! Thanks! I'll have another question tho with another field added, I posted is as an answer. Could you look please? – Piotr Nazim Jun 23 '22 at 20:28
  • @PiotrNazim - What you posted as an "answer" is an interesting, related question. It is different enough that it is its own separate question. The proper action on your part is to start a new thread (a new question), possibly with a link back to this thread for reference (for anyone who wants to see the history). Posting it as an answer in this thread is definitely not a good idea - it is **not** an answer to your question. Anyone visiting this thread in the future and seeing your additional, follow-up question posted as an "answer" will be very confused. –  Jun 23 '22 at 20:41
  • sure, thanks, I'll deleted an answer and created new topic: https://stackoverflow.com/questions/72736611/how-to-list-multiple-combinated-rows-from-one-table Would be really grateful if you look :) – Piotr Nazim Jun 23 '22 at 21:14