Alternativ to multiple AND conditions in oracle

Question

I know there is IN as alternative to multiple ORs:

select loginid from customer where code IN ('TEST1','TEST2','TEST3','TEST4'))

This will return all loginids with code that mach any of the four TEST elements. Is there something similar for AND? I will need to find out all loginids that have code: TEST10,TESTA,TEST1,TESTB,AIFK,AICK....(there are 20 codes)

Does this mean that you would have multiple rows for each loginid and you want all loginids for which there are rows for all your 20 values? — Benoit, Sep 04 '15 at 12:17
There is a short version for `where code = 'TEST10' and code 'TESTA' and code = 'TESTB'`. It's `where 1=0` because a single column can't have more than one value at a time. — , Sep 04 '15 at 12:18

score 3 · Answer 1 · answered Sep 04 '15 at 12:21

3

You cannot compare that. With ORs or IN you look for records that match one of the values. With AND you would look for a record where the column matches all those values, but this field can of course only hold one value, so will never find any record.

So obviously you are looking for something entirely else. Probably you want to aggregate your data, to find IDs for which records for each of the values exist. This would be:

select loginid 
from customer 
where code IN ('TEST1','TEST2','TEST3','TEST4')
group by loginid
having count(distinct code) = 4;

answered Sep 04 '15 at 12:21

Thorsten Kettner

89,309
7
49
73

This answer appears to be a direct clone of the following question: http://stackoverflow.com/a/15977163/1080648 without any reference to the user's particular problem.... it doesn't answer the question – Jon Story Sep 04 '15 at 12:22
2

@Jon Story: How does it not answer the question? I am explaining that `code = 'TEST1' AND code = 'TEST2'` makes no sense and elaborate why it doesn't. I also kind of derive what is really being asked and show a solution. And no, I didn't look up other answers or copied something. It is simply the straight-forward query for the problem given. – Thorsten Kettner Sep 04 '15 at 12:27

Jon Story · Answer 2 · 2015-09-04T12:27:00.153

No

A short answer, I know, but sometimes it's the only real answer.

However

Depending on what exactly you're trying to achieve, you may be able to use a count of a query grouping the items, to check that all match.

This assumes that your CustomerCodes are kept in a separate relational table.

SELECT loginID
FROM Customer
WHERE loginID IN (
    SELECT loginID, count(*) as codeCount
    FROM CustomerCodes
    GROUP BY loginID
    HAVING codeCount = 20
)

It doesn't work if you have Code1, Code2 Code3 etc fields... you'd have to split the data out into a separate table, for example:

loginID | code
---------------
1       | code1
1       | code2

score 2 · Accepted Answer · answered Sep 04 '15 at 12:21

2

You could do the following:

SELECT loginid
  FROM customer
 WHERE code IN ('TEST1', ... , 'TEST20')
 GROUP BY loginid
HAVING COUNT(DISTINCT code) = 20;

The difference between Jon's answer and this one is that if you use other codes in the table, my query will return all loginids for which there are rows for these 20 codes, and Jon's answer will return all loginids for which there are 20 distinct codes.

answered Sep 04 '15 at 12:21

Benoit

76,634
23
210
236

As you note, this doesn't answer the question – Jon Story Sep 04 '15 at 12:23
1

Sometimes a question doesn't make sense when read literally, but the actual intent can be inferred, and answering that isn't unreasonable. As you've done yourself in your 'however' section, of course. I'm not sure there's any other interpretation that makes sense in this case. – Alex Poole Sep 04 '15 at 12:33
But why would there be 10 rows for each customer? Surely each customer has one row? Or perhaps I'm assuming too much common sense from the database design... – Jon Story Sep 04 '15 at 12:43
Off the top of my head, maybe the pk is loginid and code? – Boneist Sep 04 '15 at 13:24

Alternativ to multiple AND conditions in oracle

3 Answers3

No

However