Databases
StackOverflow2013
Postsdbo
POHow to change logical disjunctive OR SQL query to conjunctive AND to match all input parameters in list?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to change logical disjunctive OR SQL query to conjunctive AND to match all input parameters in list?
    text
    copied!<p>I hope the title somehow makes sense.</p> <p>I've got 3 tables in DB. First one holds <strong><code>product_variant</code></strong> records, second one holds the <strong><code>label</code></strong> records and the third one is the <strong><code>ref</code></strong> table in between.</p> <ul> <li><code>product_variant</code>: (id, productId, serviceProviderId).</li> <li><code>label</code>: (id,label).</li> <li><code>product_variant_label_ref</code>: (label_id,variant_id).</li> </ul> <p>Input parameters for my query are: <code>productType</code>, <code>serviceProviderId</code> and set of labels and my aim is to select rows in <code>product_variant</code> table based on the input parameters.</p> <p>This is my query in hibernate xml file:</p> <pre><code> SELECT v.* FROM product_variant v INNER JOIN product_variant_label_ref r ON v.id = r.variant_id INNER JOIN product_product p ON p.productType = :productType INNER JOIN product_label l ON l.id = r.label_id WHERE v.product_id = p.id AND v.serviceprovider_id = :serviceProviderId AND l.label in (:labels) </code></pre> <p>Problem is that with <code>IN</code> operator I am getting disjunctive <code>OR</code> selection and I want <code>AND</code> in between. If I specify different labels as an input, I want only those <code>product_variant</code> rows which have a relation to all of them through ref table. If one of the input labels doesn't belong to any <code>product_variant</code>, query should not return anything. Could anyone help me to change my query? </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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