Create SQL statement with mulitple AND

I have a problem with creating right SQL request. This is my table:

item    warehouse   amount
item1   A   1
item1   B   5
item2   A   9
item2   B   2
item3   A   1
item3   A   0
item4   B   1

I would like to display all items that:

1. -Are in warehouse A in number less than 2
2. -Are in watehouse B in number grater than 0 At the same time.

I would expect item1 will be displayed.

My code so far is:

SELECT item, warehouse  , amount

FROM XXXX

WHERE 
(warehouse  = B AND amount>0)
AND 
(warehouse  = A AND amount<2)

I know that warehouse = B AND warehouse = A will never happened, but i do not have any idea where to go from here.

I’am new to SQL, but i have feeling that i need to use GRUPBY

Any help ?

Answer

You can use aggregation. Use where to get either condition. Then check that both are met. Assuming that there is one row per item per warehouse, you can use:

SELECT item
FROM XXXX
WHERE (warehouse = 'B' AND amount > 0) OR
      (warehouse = 'A' AND amount < 2)
GROUP BY item
HAVING COUNT(*) = 2;

Note that this does not return the amounts. If you want that as well, then pivot the data using conditional aggregation:

SELECT item,
       SUM(CASE WHEN warehouse = 'B' THEN amount END) as b_amount,
       SUM(CASE WHEN warehouse = 'A' THEN amount END) as a_amount
FROM XXXX
WHERE (warehouse = 'B' AND amount > 0) OR
      (warehouse = 'A' AND amount < 2)
GROUP BY item
HAVING COUNT(*) = 2;