Questions tagged [where]
The `WHERE` clause is used to specify criterion for retrieval or modification of rows from objects in a database.
139
questions
1
vote
2answers
28 views
Under what circumstances would these 3 queries return different number of results?
Hello I have the following question:
Query 1:
SELECT b.id,
(
SELECT SUM(cb.invoice_amount) AS sum
FROM payment_lines cb
WHERE cb.batch_id = b.id
) AS batch_total_invoice_amount
...
0
votes
1answer
20 views
Subquery with variable in where
I don't know why but if I do the subquery where in the first LEFT JOIN with the value of the variable @id it does not return any results, it is as if the value of @id was always null.
But why not do ...
0
votes
2answers
21 views
Update field with the longest varchar field
I need to update on of the varchar fields in a table, but only the record that is the longest varchar (has the most data in it.). I could easily find it with this query:
SELECT TOP(1) options
FROM ...
0
votes
2answers
311 views
Unknown column in 'where clause' when using an alias for a subquery in MySQL
This simple query:
select exam.id as exam_id,
(select count(*) from dataset where dataset.id = exam.id) as n_dataset
from exam
where n_dataset = 0
returns the following error:
ERROR 1054 (42S22) at ...
0
votes
1answer
28 views
Is there any better way to list all the databases in Postgres except database named postgres?
SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn = true And datname NOT IN ('postgres')
Using the code above, I get all the databases except postgres as shown in the image ...
0
votes
1answer
42 views
Materialized view logs — with condition?
I've created materialized view logs on a WORKORDER table in an Oracle 19c database.
create materialized view log on my_workorder_system.workorder with rowid;
The plan is to integrate the WORKORDER ...
2
votes
1answer
23 views
Check the result of a math expression in mysql
Let's say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means arithmetic operation (+ - * /) here.
a b oper c
-------------
2 3 + 5
4 2 / 3
...
2
votes
2answers
56 views
Where Clause for Results Appearing after Occurence
I'm wondering if it's possible to find records that only appear after the first occurrence of a where clause. I'm currently using PostgreSQL for this example but something not PostgreSQL specific ...
0
votes
2answers
28 views
(Beginner) MYSQL: What can I do about this where statement?
Suppose I have some table named tableA and column1 in tableA has some null values and strings.
Also suppose I had created some temporary table named tempTable that has only 1 column. And that 1 column ...
-1
votes
1answer
41 views
Need to filter by month
I am using SQL Server Management Studio.
I am trying to filter by month using dtm DATETIME as my format XX/XX/XXXX. I am very new to SQL, so I was trying to just filter by the month of January 01 with ...
0
votes
0answers
54 views
How to filter MSSQL Xevents by attach_activity_id.guid
I need to create an Extended Events session with the TRACK_CAUSALITY = ON parameter to catch errors in the batch that is executed after RPC, that's why I use this parameter.
I used this script.
When ...
2
votes
1answer
619 views
Does using inequality (<>) or NOT in where clause lead to SQL ignoring the index?
I found this article that talks about avoiding using <> operator in where clause because optimizer ignores the index.
https://www.mssqltips.com/sqlservertutorial/3203/avoid-using-not-equal-in-...
1
vote
1answer
45 views
Function that loops through array parameter values to build multiple WHERE clauses (postgres 11.4)
I have been iterating on an idea presented by Rob Conery in his excellent post to generate monthly reports using PostgreSQL Views.
My version required the View to be refactored into a Function as I ...
0
votes
0answers
29 views
SQL Trigger fails retrieve new record in second query
Any advice on this will be appreciated
This script created a table with new records.
My script is failing to assign the "Inserted" ID to the query but manually assigning a value works
(a) ...
1
vote
1answer
39 views
CASE in WHERE clause - retuns a single-row result instead of 4 rows?
Everyone,
I do not want to bother you with the whole tables schema so I will paste a couple of screenshot only so that my question becomes clear - I need to show all projects that are related to ...
0
votes
1answer
17 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
38 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
96 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, ...
2
votes
1answer
118 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
46 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
21 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
31 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
27 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
vote
2answers
1k 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
72 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
37 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
150 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
38 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
44 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
78 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
1k 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
55 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
48 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
29 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
59 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
47 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
36 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
41 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
527 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
122 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
27 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
32 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
40 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
6k 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
3k 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-...
