Postgresql no ideas to select query

I have 3 tables like this:

CREATE table materials
 (id serial primary key not null,
 name varchar(50) not null,
 unit varchar(10) not null default 'шт',
 price decimal(12, 2) not null check (price>0));

CREATE table warehouses 
(id serial primary key not null,
 lastname varchar(25) not null);

CREATE table materials_in_warehouses
 (id_warehouses integer references warehouses(id) on update cascade on delete cascade,
 id_materials integer references materials(id),
 unit varchar(15) default 'шт',
 count integer not null CHECK (count>0),
 lastdate date not null, 
primary key (id_warehouses, id_materials);

I need to select all materials with the price of > 200 , such that: on each warehouse this material is available in an amount of > 100.

The problem is that the condition we have to fit all the warehouses with this material, not any one. I have no ideas.

For example, i have:

materials 
id     name     price    unit
---    ------   -----    -----
3      Silver   300      kg

warehouses
id     lastname   
----   --------- 
2      Forman
3      Tramp

materials_in_warehouses
id_materials     id_warehouses   count  lastdate
------------     -------------   -----  --------
3                3               300    2015-12-20
3                2               200    2015-12-20

...and i want to see Silver in my result, but if i add to table materials_in_warehouses where count was < 100, as a result, silver should not be.

This is my example query, but it is not suitable for the condition

select materials.name
  , materials.price
  , materials.unit 
from materials
  , materials_in_warehouses 
where price > 200 
 AND id = id_materials 
 AND count > 100;

Solution

You can solve this with subqueries like this:

First case, count > 100 at each warehouse

SELECT * FROM materials WHERE id IN (
    SELECT m.id FROM 
        materials_in_warehouses AS mw INNER JOIN 
        materials AS m ON (m.id = mw.id_materials) 
    WHERE 
        m.price > 200 AND 
        mw.count > 100
    GROUP BY id
    HAVING COUNT(*) = (SELECT COUNT(*) FROM warehouses)
);

Second case, sum of all warehouse count > 100

SELECT * FROM materials WHERE id IN (
    SELECT m.id FROM 
        materials_in_warehouses AS mw INNER JOIN 
        materials AS m ON (m.id = mw.id_materials) 
    WHERE 
        m.price > 200 
    GROUP BY id
    HAVING SUM(mw.count) > 100
);

OK, third case, all materials where price > 200 AND count in each warehouse > 100

SELECT m.* FROM 
    materials_in_warehouses AS mw INNER JOIN 
    materials AS m ON (m.id = mw.id_materials) 
WHERE 
    m.price > 200 AND 
    mw.count > 100
GROUP BY id

As simple as possible