Questions tagged [where]
The `WHERE` clause is used to specify criterion for retrieval or modification of rows from objects in a database.
124
questions
0
votes
1answer
15 views
Inconsistent ResultSet from Procedure with Variables
I'm working on building a procedure with two inputs, and the Syntax checks out, but the result-set is not consistent with the logic.
In this question, there are in actual fact two questions. Number 1 ...
0
votes
1answer
13 views
How does adding LIMIT work with Where conditions in mysql?
In my database, there is only one record which satisfies the condition iv_title = 'ball skills'.
Now I'm executing the following query :
SELECT
*
FROM
`imageAndVideo`
WHERE
`iv_title` = '...
5
votes
2answers
88 views
More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows
I have some queries where I need to get more than 64 specific rows, like this example with 65 IDs.
TableID is primary key, type BigInt.
SELECT * FROM TableA
WHERE TableID IN (260905384, ...
1
vote
1answer
21 views
How to check other table for value during insert
In PostgreSQL is it possible to check another table's data to see if there is data that fits a certain condition during an INSERT statement?
I have attempted to use WHERE NOT EXISTS as I want to not ...
0
votes
1answer
44 views
How to use WHERE condition when using ON clause [closed]
I have looked at similar posts and have tried them but dint seem to work. In the below query on a MSSQL 2015 database, I need to filter out inactive users. That is, only return results where 'Active' =...
0
votes
1answer
20 views
Performance of query from this schema
Essentially all comments are stored in this schema (dbml)
table comments {
commentID BINARY(16) [not null] //UUID
post_date timestamp
postID BINARY(16) [not null] //UUID
has_reply bool [...
0
votes
0answers
26 views
Speed up query on large MySQL table
SELECT property, geo, stars
FROM table
WHERE SUBSTRING_INDEX(geo, '>', 3) LIKE '%$mylocation%'
...loop to show results
geo column has values like - Spain>Canary Islands>Lanzarote>Some ...
-1
votes
2answers
26 views
UPDATE with too much values at IN state takes too long
I have the next query:
UPDATE plates_owners SET deleted=1 WHERE plate_id=4748 AND branch_id IN
(23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,...
-1
votes
2answers
212 views
Perform TABLESAMPLE with WHERE clause in PostgreSQL
I want to use TABLESAMPLE to randomly sample from rows that meet a certain condition with PostgreSQL.
This runs fine:
select * from customers tablesample bernoulli (1);
But I cannot figure out how ...
0
votes
2answers
36 views
How to select Overdue Rows with Date Frequencies?
+------------------------+--------+
| Invoice_id | due_date | amount |
+-------------+----------+--------+
| 20 |2020-01-18| 1250 |
+-------------+----------+--------+
| 21 |2020-...
-2
votes
1answer
19 views
Can't manage to work with comparing days
In a table with persons and their birthdays, the ones whose birthday is in less than 7 days need to be displayed.
I found some answers on the Internet but they all are at least 5 years ago with ...
-1
votes
2answers
33 views
Select Rows From One Table, Where Matching Rows in Another Table Has Empty Column
I have multiple Postgres tables that look like the following. They are generated on a daily basis, so each just represents the data that was processed for that day:
data_20200113
+-----------+-------...
0
votes
1answer
40 views
bridge table joined table have both the keys equal to both the keys
SELECT DISTINCT i0.id,
i0.name
FROM main_table AS i0
RIGHT OUTER JOIN "bridge_table" AS i3 ON i3."main_table_id" = i0."id"
RIGHT OUTER JOIN "other_table" AS r4 ON i3."other_table_id" ...
0
votes
1answer
25 views
Time interval is returning all records or no records
I am trying to run a query to return records in the last 24 hours and following the instructions here: time interval tutorial
After running the query at 18:30 UTC, my query looks as follows:
RETURN ...
1
vote
1answer
31 views
Null check condition in Where clause
DECLARE @SubDesc1 nvarchar(100)
SET @SubDesc1 = null
I would like to do something like that.
Query 1
SELECT *
From Table1
where Category = 'Category1'
AND [Description] ='ABC'
AND ...
0
votes
1answer
17 views
Does range improve match operation and which runs first, range or match
I am using MySQL version 5.6.10
I have a very very large table large_table and a relatively small table small_table
The small_table has only the records of the date 2019-11-20
There is a column I ...
0
votes
1answer
38 views
select subquery condition in where statement
I am getting this error:
ORA-06550: line 43, column 16: ORA-00904: "STOCK_BALANCE": invalid identifier
...for the following query:
select PARTY_ID,
PARTY_NAME,
IT_ID,
IT_ID ||...
0
votes
3answers
77 views
How to avoid tons of lower(column) in WHERE clause for several LIKE comparisons?
I've got to filter data in a string column and thus need to compare the column using NOT LIKE with a bunch of strings.
I'm using SQL Server and my code looks like this:
SELECT history.date,
...
0
votes
1answer
397 views
How to use comma separated columns in CASE WHEN Statement?
I am trying to add below CASE WHEN Statement in SQL WHERE Clause, But SQL Server not allowing me to do so. All column used are of same Data Types but still its giving me error, Can anyone please check ...
1
vote
1answer
38 views
WHERE IN subquery too slow
I have a FULLTEXT search on the database, with the WHERE IN condition which checks that search results exists in another table. The query takes more than 1 hour to complete (companies table has 67k ...
0
votes
1answer
39 views
Postgres: strange long request: A is fast, B is fast, A WHERE IN B is slow
There is a lot of rows in table "logs" (the Request A FROM).
Request A is quick and Request B is quick.
Put together, Request A WHERE IN Request B is very long. Is that normal?
Request A is very ...
0
votes
1answer
28 views
SQL order of precedence
Given a query ending in where a and b and c or d, will this behave as where (a and b and c) or d, or where a and b and (c or d)? (Or something else?)
I've read the docs and they seem to suggest it ...
0
votes
1answer
52 views
Where clause in select does not work with Inequality of guid
I want to query our CRM Db.
I have a backup of CRM Db and the existing one.
There is a custom entity,in both of them I have created.
Some of my custom entity records has deleted from the existing db.
...
1
vote
2answers
46 views
How to differentiate between the result sets retrieved for multiple OR conditions?
I've a huge dataset which I'm quering with multiple OR conditions. Though I'm getting all the results, but I want to differentiate between the results that are retrieved for a certain where condition. ...
0
votes
0answers
30 views
Sql, get data/rows for more selected ids in one condition, from file
I am using PostgreSQL 11.5 on Ubuntu
Question
How can I get data/rows for more selected ids in one where condition? Ideally from file with list of ids.
Example
|product_id |product_name ...
1
vote
1answer
35 views
Optimization on table selected where primary key
I have this query:
SELECT *
FROM wp_dev_posts, wp_dev_postmeta, wp_dev_term_relationships
WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'))
AND (wp_dev_posts.ID = ...
-1
votes
2answers
39 views
How to get column values in where condition in sub query? [closed]
I am trying to get distinct list of values in where condition through sub query but it shows 0 rows in result set. Here is my query.
SELECT a.topic_id, t.id as topicid, t.value as value, t.topic as ...
0
votes
0answers
32 views
How to get the data using where_in (1,3 ) table in mysql?
We have 2 Tables
Table 1 is User
Table 1:
id | name | sub_code
-----------------------------
1 | name1 | 1
2 | name2 | 1,3
3 | name3 | 2,4
Table 2 ...
1
vote
2answers
198 views
IN clause not working on mysql 8.0.17
I was on mysql:8.0.15 and I updated to mysql:8.0.17
On 8.0.15 ... and (col1, col2) in (('a', 'b')) the query return results, this is what I expect
On 8.0.17 the same query with same data return 0 ...
0
votes
3answers
41 views
Order of WHERE IN result for PostgreSQL
If I use the following selection
SELECT * FROM users WHERE id IN (3,1,2)
Can I expect the result to be in the same order like the provided ids?
0
votes
0answers
25 views
How performant is finding a substring in database?
To route requests on my website, I need to query the database to see if the first part of a URL matches a category for an item.
For example:
I have a request to /animals/dog/darktheme/. The ...
1
vote
1answer
30 views
How to order by then start at first non null value and select a limit for number of values
I have an environmental logger and I am trying to select every 5th row starting at where outsidetemp is not null (I want to include the null values after the first not null value). Is there a way to ...
3
votes
2answers
33 views
Optimizing PostgreSQL where clause with OR
I have a query that uses an or in the where clause. It is slow. It seems that re-writing the query using a union could get the same results much quicker. But I'm in an environment where we generate ...
0
votes
1answer
4k views
CASE in WHERE clause in MySQL
I'm working in a project with MySQL and using stored procedures to make my SELECT queries,
so all of them have this same structure:
DELIMITER $$
CREATE PROCEDURE `case_in_where`(IN `column_selector` ...
0
votes
2answers
1k views
Mysqldump multiple tables with where clauses in single transaction
Suppose, we have 2 tables: t1,t2 in database db:
Expected the below code to dump tables based on conditions specified in the where clauses:
mysqldump --login-path=login_path \
--single-...
0
votes
1answer
18 views
Where clause no give expected result
where clause not show expected result.
declare @PracticeId bigint =1000
select * from insurance i
WHERE ISNULL(I.DELETED,0)=0
and (
( @PracticeId =1000 and(I.PracticeId=@PracticeId ...
0
votes
1answer
261 views
How to search multiple values using LIKE
I'm using MySQL and I have a table with these fields :
id
name
kode_fakultas
year
I want to create a form for searching based on user input. Example, if the user search is based on name, but the ...
9
votes
2answers
8k views
Using column alias in a WHERE clause doesn't work
Given a table users with two fields: id and email.
select id, email as electronic_mail
from (
select id, email
from users
) t
where electronic_mail = ''
Postgres complains that:
...
0
votes
1answer
44 views
MySQL under what scenario suitable to use WHERE EXISTS
I recently just noticed that WHERE EXISTS can slow down the operation badly. E.g.
select SQL_NO_CACHE count(*) as aggregate
from `table_1`
where exists (select *
from `table_2`
...
0
votes
2answers
73 views
How to efficiently handle querying for different combinations of optional parameters
Consider the following Tables:
Table: Parts
part_id INT PK
part_number VARCHAR
Table: Jobs
job_id INT PK
job_number VARCHAR
Table: Job_Parts
part_id INT FK PK
job_id INT FK ...
2
votes
1answer
79 views
where clause after limiting results
I have a question about getting results after limiting the rows.
I don't have much experience with MySQL, but I'm trying to get results from a database where I need to do the where clause after ...
1
vote
2answers
263 views
SELECT i.c.w. a COUNT and INNER JOIN gives different results with string literals and numeric values when they are used in a WHERE clause
I have a MySQL query:
SELECT
p.informationProvider as pGLN,
p.productID,
p.productStatus
FROM tblproducts AS p
WHERE
-- informationProvider is of type varchar(14)
p....
0
votes
1answer
100 views
In where clause, does MySQL continue checking conditions if first condition before AND falsy
I have a question about and AND inside a where clause of a query. Within the where clause, I have a subquery but it is after a few other, row value conditions, like validated = 1 AND (subquery). I ...
0
votes
2answers
40 views
How space is compared in where condition
I've come across below query where it is column being compared the space character.
SELECT *
FROM company
WHERE emp_name > ' ' -- space char
AND emp_id = 123
AND active_flag = 'Y';
...
2
votes
1answer
65 views
Why does MySQL optimize query with “WHERE … OR” so badly?
Hey there,
this is my first question so please be patient if I miss to add something in here.
My question is why the OR in the WHERE clause causes the query to be executed the way it is (which is ...
0
votes
2answers
8k views
LEFT JOIN with WHERE (Microsoft Access SQL)
Given following 2 tables a, b:
table a
ID job
1 farmer
2 physician
3 bank clerk
table b
bID ID lang job
1 1 en farmer
2 2 en physician
3 3 en bank clerk
4 2 de Arzt
5 1 ...
-1
votes
1answer
50 views
Need Help With View Where Condition
I am creating a View to grab contact information from my database. This is the code I have for the view at the moment:
CREATE VIEW SupplierContact AS
SELECT
sc.first_name + ' ' + sc.last_name AS ...
0
votes
2answers
193 views
Sql Substring and Search String
I have a table in my database for storing supplier contacts and it is laid out like so:
Table: supplier_contacts
contact_id INT IDENTITY(1,1)
first_name VARCHAR(50) NOT NULL,
last_name ...
2
votes
2answers
122 views
In what direction SQL Server WHERE clause conditions are checked?
I have this query:
SELECT RCodeID as 'ListItemID',
RCode as 'ListItemName'
FROM RCode
WHERE RCode not like '%,%'
AND ISNUMERIC(RCode) = 1
AND CAST(RCode as int) >= CAST(@...
0
votes
1answer
91 views
Update + where not exist
once again, I come here for your rescue.
how can I improve a query like this for a table with 20 million rows?
I'm trying to update a table, by using the date and getting the first date of ...
