select 2 recors in case 2 exists-CodePudding

I have 2 rows

code name
1   cake
2   chocolate

This query give me both results

select * from table a  where a.code=2 or a.code =1

If one of these records doesn't show, I don't want to retrieve anything.

select * from table a  where a.code=2 or a.code =1
and exists ( select 1 from table b where a.code=b.code )

CodePudding user response：

You can use an analytic function:

SELECT code,
       name
FROM   (
  SELECT a.*,
         COUNT(DISTINCT code) OVER () AS num_codes
  FROM   table_name a
  WHERE  a.code IN (1,2)
)
WHERE  num_codes = 2;

Which, for the sample data:

CREATE TABLE table_name (code, name) AS
SELECT 1, 'cake'      FROM DUAL UNION ALL
SELECT 2, 'chocolate' FROM DUAL;

Outputs:

CODE NAME

1 cake

2 chocolate

CODE	NAME
1	cake
2	chocolate

If you:

DELETE FROM table_name WHERE code = 1;

And run the query again, it outputs:

CODE NAME

db<>fiddle here

CodePudding user response：

Simple check the count distict

select * from tab1
where code in (1,2) 
and (select count(distinct code) from tab1 where code in (1,2)) = 2;

If you want to discard the case where the table has duplicated rows, e.g. 1,1,2

add an other predicate filter

 and (select count(*) from tab1 where code in (1,2)) = 2