Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Without any backing schema or even a database manufacturer, my response will have to make some assumptions about your setup.</p> <p><strong>Theory</strong></p> <p>This sounds an awful lot like a Cartesian product. The crux of your problem is that your IN clauses are both independent of one another. Thus, a single record can satisfy both conditions but not be what you want to see in the result set. As an example say we have a record as follows:</p> <pre><code>id name typeOfEcosystem service -- ------------ --------------- ---------- 1 myEcoService Freshwater Recreation </code></pre> <p>Your query, as it is written now, will first take the typeOfEcosystem (Freshwater) and check if it is in your list, which it is. <strong>Result: TRUE</strong></p> <p>Then, the query will take the service (Recreation) and check if it is in the second list. Again, the value is found. <strong>Result: TRUE</strong></p> <p>All conditions evaluate to true, so the row is included in the final result set.</p> <p><strong>Solution</strong></p> <p>It sounds to me like you want a multiple column IN clause. Note that this is distinct from your attempt, as the final solution should only involve a single IN keyword. Now, the exact form of the query will depend a lot on your database and what it provides. A solution that should work on any SQL-compliant database is to use string manipulation. A rough example is as follows:</p> <pre><code>Select * from Gecoserv _a where _a.typeOfEcosystem || '-' || _a.service IN ('Freshwater-Habitat', 'Saltwater-Food', 'Dunes-Recreation') </code></pre> <p>In this example, '||' is assumed to be concatenation. This is very portable, but it is SLOW. The problem can be tackled much more elegantly, but the exact solution will depend a lot more on your specific database and requirements. For example, a multiple column in clause in Oracle might be as simple as:</p> <pre><code>Select * from Gecoserv _a where (_a.typeOfEcosystem, _a.service) IN (('Freshwater', 'Habitat'), ('Saltwater', 'Food'), ('Dunes', 'Recreation')); </code></pre> <p>You may have to massage any of these solutions to get them to play well with your DBMS. Further techniques (and just plain old god reading) can be found at:</p> <ol> <li><p><a href="https://stackoverflow.com/questions/13027708/sql-multiple-columns-in-in-clause">SQL multiple columns in IN clause</a></p></li> <li><p><a href="https://stackoverflow.com/questions/9430411/select-records-where-a-combinations-of-columns-are-repeated-using-sqlite">Select records where a combinations of columns are repeated using SQLite</a></p></li> </ol>
    singulars
    1. This table or related slice is empty.
    1. POSQL Search Query with multiple columns needs to edited
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USphobos51594
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThanks for the answer. However, what I want to do is not writing the elements one by one. The first query you wrote is working great but there should be a way to take elements who have the same rank. (For example: typeNameList[4] and serviceList[4] or typeNameList[28] and serviceList[28]) So, what I'm looking for is the query enables us to select the items from array variables; typeNameList and serviceNameList. But the trick is the elements should have the same rank.
      singulars
      1. PO
      1. USMelih Ozturk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload