0

How would I search multiple (in this example, say 2) columns for an array of values?

For example, here is my table Documents :

1. DocNumber (varchar)
2. CompanyCode (varchar)
3. Data1 (varchar)
4. Data2 (varchar)
5. Data3 (varchar)

DocNumber and CompanyCode form the composite primary key of this table. Say I have a set of values which I want to search in the database such as:

DocNumber CompanyCode
1001 101
1002 102
1004 103

How would I find these unique combinations in the table with one query?

I could use in:

select *
from Documents
where DocNumber in :docNumbers and CompanyCode in :companyCodes

But that would also return records with DocNumber 1001 and CompanyCode 102 (all combinations of the 2 lists). I want to avoid that.

I am using HANA DB (through a Spring Boot application).

Sample table data:
table data

Expected response:
expected response

Response I get:
current response

philipxy
  • 14,867
  • 6
  • 39
  • 83
Sanchit Snehashish
  • 11
  • 1
  • 6
  • 2
    Add some more sample table data and also _specify_ the expected result - a [mcve]. – jarlh Jan 25 '23 at 20:52
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) Please don't insert "EDIT"s/"UPDATE"s, just make your post the best presentation as of edit time. – philipxy Jan 25 '23 at 22:57
  • Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad definition/query/function with the obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & ideally a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] – philipxy Jan 25 '23 at 22:58
  • (Clearly.) When specific this will be a faq. Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help]. Reflect research in posts. – philipxy Jan 25 '23 at 23:05
  • You don't clearly say or give an example of what you want, but returning subrows of a table that appear in it with at least the subrows of another table is [tag:relational-division]. This & related queries, eg, exactly the subrows, are faqs. But one must pin down via a [mre] & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably. [Select values that meet different conditions on different rows?](https://stackoverflow.com/q/477006/3404097) – philipxy Jan 26 '23 at 01:25

1 Answers1

0

The IN predicate also works with tuples. Please find a minimal example:

SELECT * 
FROM 
(
    SELECT 'A1' AS DOCNUMBER, 'A2' AS COMPANYCODE FROM DUMMY
)
WHERE (DOCNUMBER, COMPANYCODE) IN (('A1', 'A2'),('B1', 'B2'))

Further details and examples can be found in the documentation.

Mathias Kemeter
  • 933
  • 2
  • 11