No you can’t use case and in like this. But you can do SELECT * FROM Product P WHERE @Status=”published” and P.Status IN (1,3) or @Status=”standby” and P.Status IN (2,5,9,6) or @Status=”deleted” and P.Status IN (4,5,8,10) or P.Status IN (1,3) BTW you can reduce that to SELECT * FROM Product P WHERE @Status=”standby” and P.Status … Read more
To make effective use of the index, we could rewrite the IN predicate example (x0, y0, x1, y1) IN ((4, 3, 5, 6),(9, 3, 2, 1)) Like this: ( ( x0 = 4 AND y0 = 3 AND x1 = 5 AND y1 = 6 ) OR ( x0 = 9 AND y0 = 3 … Read more
Use FIELD(): SELECT * FROM table WHERE id IN (5,4,3,1,6) ORDER BY FIELD(id, 5,4,3,1,6);
I’ve found the solution!! 🙂 var qOver = _HibSession.QueryOver<MyModel>(() => baseModel) .JoinAlias(() => baseModel.Submodels, () => subModels) .WhereRestrictionOn(() => subModels.ID).IsIn(IDsSubModels) .List<MyModel>();
For the IN operator, you want a tuple instead of list, and remove parentheses from the SQL string. # using psycopg2 data=(‘UK’,’France’) sql=”SELECT * from countries WHERE country IN %s” cur.execute(sql,(data,)) During debugging you can check that the SQL is built correctly with cur.mogrify(sql, (data,))
The subquery is being run for each row because it is a correlated query. One can make a correlated query into a non-correlated query by selecting everything from the subquery, like so: SELECT * FROM ( SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1 ) AS subquery The final query would look … Read more
You’ll have to construct the query-string. <?php $ids = array(1, 2, 3, 7, 8, 9); $inQuery = implode(‘,’, array_fill(0, count($ids), ‘?’)); $db = new PDO(…); $stmt = $db->prepare( ‘SELECT * FROM table WHERE id IN(‘ . $inQuery . ‘)’ ); // bindvalue is 1-indexed, so $k+1 foreach ($ids as $k => $id) $stmt->bindValue(($k+1), $id); $stmt->execute(); … Read more