How do I select with where column have two different value (Oracle)-CodePudding

I'm learning SQL, I'm a bit complicated with a sentence that I have to do.

I need to get the accounts, which have these values in the same columns (KEY)

1133-1-1, 7095-1-1

Code:

SELECT cta 
FROM cargos 
WHERE key = '7095-1-1' AND key = '7021-233-1';

Expected result:

cta: 192568210

The result of this query is 0, but If I make the query to get the keys of an account, it shows them to me:

SELECT key 
FROM cargos 
WHERE cta = 192568210;

Result:

I use Oracle 11g.

CodePudding user response：

The key cannot be '7095-1-1' and '1133-1-1', because it can have only one value at the same time. But it can be '7095-1-1' OR '1133-1-1'.

The confusion arises from the fact that the condition does not specify which rows are returned (row1 and row2), but which condition each individual key must satisfy for a row to be returned.

SELECT cta FROM cargos where key = '7095-1-1' OR key = '1133-1-1';

CodePudding user response：

You can use OR as already mentioned. Another option is to use IN. This will be much shorter and better readable if there are plenty of OR's otherwise. For example:

SELECT cta FROM cargos 
WHERE key IN ('7095-1-1','7021-233-1','123','ABC');

will do the same as

SELECT cta FROM cargos
WHERE key = '7095-1-1'
OR key = '7021-233-1'
OR key = '123'
OR key = 'ABC';

For just two keys, it doesn't really matter which of these two options you choose.

CodePudding user response：

use or instead of 'and'

SELECT cta FROM cargos where key = '7095-1-1' or key = '1133-1-1';