CODEWITHSUNDEEP
sqloracle-database
mohamed
mohamed

Reputation: 149

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

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:

enter image description here

I use Oracle 11g.

Upvotes: 0

Views: 420

Answers (3)

Jonas Metzler
Jonas Metzler

Reputation: 5975

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.

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112324

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';

Upvotes: 1

Learn Hadoop
Learn Hadoop

Reputation: 3050

use or instead of 'and'

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

Upvotes: 1

Related Questions