Select data from query1 where option = and != together-CodePudding

help me please.

I having result set (table). To get the this first result set, I make a selection with three joins. To get all the necessary characteristics of the goods ( 05_A and 05_B and some descriptions)

We can see stock items.

for example:

artid | warehousetype 
001   | 05_A
001   | 05_B
002   | 05_A

I need to select all artid where warehouse type in 05_A and not in 05_B (ex. 002)

I'm try: select * from table where warehousetype like 05_A and warehousetype not like 05_A - it's not work.

I'm try

select * from table where warehousetype like 05_A
MINUS
select * from table where warehousetype like 05_B

it's works but i'm see all stock w\o 05_B, but need see only artid where whtype = 05_A and not 05_B

How do make my report?

CodePudding user response：

You can use the NOT EXISTS clause along with a subquery to achieve the desired result.

SELECT artid
FROM table
WHERE warehousetype = '05_A'
AND NOT EXISTS (
    SELECT artid
    FROM table
    WHERE warehousetype = '05_B'
    AND table.artid = table.artid
);

You can also use a JOIN with a negation condition on the joined table to get the same result.

SELECT t1.artid
FROM table t1
LEFT JOIN table t2 on t1.artid = t2.artid and t2.warehousetype = '05_B'
WHERE t1.warehousetype = '05_A'
AND t2.artid is null

CodePudding user response：

You can use Oracle's MINUS operator, to subtract all "artid" values that have '05_B' from all records that have '05_A'.

SELECT artid FROM tab WHERE warehousetype = '05_A'
MINUS
SELECT artid FROM tab WHERE warehousetype IN ('05_B')

Check the demo here.