sql

SQL: query to select all records from join table


I have join table (from relation OneToMany-ManyToOne):

warehouse_group_id warehouse_id
1 1
2 1
4 1
4 2
5 1
5 2

I want to receive list of all warehouse_id for warehouse_id 2. For example for warehouse_id = 2 I want to know all warehouse_id values that belong to one warehouse_group_id. Eg. for warehouse_id = 2 I want to receive warehouse_id 1,2 because warehouse_id = 2 is part of warehouse_group_id 4 and 5 and in warehouse_group_id 4 and 5 there are warehouse_id 1,2

What query should I prepare?


Solution

  • You can use 'self join' technique:

    select distinct wh.warehouse_id
    from w 
    join w as wh on w.warehouse_group_id = wh.warehouse_group_id
    where w.warehouse_id = 2;
    

    Try it on fiddle