All Questions
284 questions
0
votes
0
answers
20
views
Variable only resets per row when query has a static IN condition
I have a complicated query. I have a bunch of users. I have a ledger of debits and credits. I want to filter a user's subscribers by how long the subscribers have been negative. To do that I find ...
- 101
0
votes
1
answer
37
views
using field of subquery in output
Query:
SELECT tbl1field
FROM tbl1
WHERE id = (SELECT t2id
FROM tbl2
WHERE anotherid = (SELECT t3id FROM tbl3 )
)
here tbl3 is having 2 fields : t3id and ...
- 101
0
votes
1
answer
27
views
Get difference in values of second table from IDs in two columns of first table?
Given these two tables:
Table1
id1
id2
224
2341
5234
9394
6243
2342
⋮
⋮
Table2
id
value
224
2
2341
52
2342
24
5234
23
6243
242
9394
99
⋮
⋮
I would like to create this table:
Table3
id1
...
- 103
1
vote
3
answers
416
views
Need help rewriting view to avoid using subqueries in MySQL 5.1 db
I am working on creating a view in MySQL for an application that tracks form submissions. There is one particular table that I cannot figure out how to write a query for. It has foreign_keys ...
- 19
0
votes
1
answer
54
views
Combine tables to find date of last value changed
Trying to query the date that a field changed to a zero amount. Here is some of the data from the tables involved:
-- mbMaster is the master table for all 'm' type tables (mvTable, mcTable), but does ...
- 75
1
vote
2
answers
67
views
Query to return latest record between 2 tables
I'm trying to pull the most current date (cusPurDt) and corresponding amount (subAmt) by customer (cusID/subID). Here is some sample data:
create table customer (
cusID char(5),
cusPurDt ...
- 75
1
vote
1
answer
38
views
MySQL Tennis Ladder View Creation Question
I am creating a Tennis Ladder VIEW using MySQL and wish to do the following:
Properly create a view that includes: All player's names
These player names will come from the table: User - using the ...
- 21
0
votes
0
answers
32
views
How to use a parameter derived from a Table, given I'm filtering the table in query
The task is to find what fraction of total users have logged in at least for one more consecutive day after their first login.
SELECT
ROUND( COUNT( b.player_id )/COUNT( DISTINCT (SELECT a....
0
votes
1
answer
103
views
The most recent rows in for each type/agent
I have a table (still on MySQL 5.7) with lots of data. This table is written by some scripts. It works like a "checkin", where each agent is checking in in some period of time. It looks like ...
- 13
1
vote
2
answers
92
views
MySQL with subquery becomes slow
I have 3 MySQL tables: products, orders and users.
I want to show all ordered products where not all products are "pre-orders" or "discounts". Note: one user can have multiple ...
0
votes
2
answers
112
views
Using a WHERE colname NOT LIKE (select * from otherTable)
So in essence what im trying to do is this:
Given TBL1 (around 8,000 rows)
letters
abababa
bcbcbcb
cdcdcdc
and TBL2 (around 2,000 rows)
letters
ab
bc
I want to write a query to find every row ...
2
votes
1
answer
74
views
How to calculate latlong difference from last 15 min ago and last 1 min ago without using correlation subqueries
Problem
I would like to find logs that are active and do not hit a distance quota of 12.5km, comparing from last 15 min ago and latest (within last 1 min ago)
Sample data
select version();
version()
...
- 79
0
votes
1
answer
120
views
optimization with subquery not working as expected
I have a MySQL InnoDB database. One table called affymetrixProbeset contains more than 300 million rows.
Querying this table with INNER JOIN to other tables, with an ORDER BY and offset/limit takes ...
1
vote
1
answer
885
views
How to GROUP BY boolean when im already grouping by time frames 8hr/11hr/14hr/17hr/20hr in MySQL
I need to group 2 tables (Key = analysis_id <=> id) where my output should show the last week or month or year, grouped by a Time frame and grouped by bolean "R" or "L". I ...
- 29
0
votes
0
answers
24
views
Mysql Query need to optimize
I have below query having proper indexes (all columns in where clause), But still query is very slow.
Can anyone help me to optimize this mysql query?
SELECT
centerID,
compID,
...
- 1
0
votes
1
answer
377
views
How to group by week/month/year and time frame 8hr/11hr/14hr/17hr/20hr in MySQL
I need to group 2 tables (Key = analysis_id <=> id) where my output should show the last week or month or year, grouped by a Time range. I have around 4000 inputs from users.
Example of tables ...
- 29
0
votes
1
answer
891
views
Why cannot I reference the second alias from a subquery containing a UNION?
Why cannot I use Qty2 as an alias in my query? I receive this error:
Error Code: 1054. Unknown column 'qty2' in 'field list'
SELECT mat, qty, qty2, SUM(qty), (ifnull(SUM(qty - qty2),0)) as TotalQty
...
0
votes
0
answers
363
views
MySQL query: multiple select with different conditions and LIMIT for each, but also LIMIT whole query + paginator friendly
I would like to write SQL query (for MySQL) that is able to SELECT
x rows by condition1 from table1
y rows by condition2 from table1+join1
z rows by condition3 from table1+join2
But on every page (...
- 1
2
votes
2
answers
267
views
SELECT groups by individual members AND sum other values
Using MySqL 5.7.34 and having a table, groups_members, with 4 columns
ID
group
member
organization
1
A
Bob
Apple
2
A
Susan
Apple
3
A
Joe
Apple
4
B
Steve
Microsoft
5
B
Mike
Microsoft
6
B
Bob
...
- 123
0
votes
1
answer
480
views
MySQL query: Detect different rows between two tables and show differences in result table
I have two tables in a MySQL database.
Table1
a_id
1
3
4
5
6
Table2
b_id
1
2
3
I would like to spot the missing row from Table1 a_id 2, and missing rows from Table2 b_id 4,5,6, which should be shown ...
- 157
1
vote
0
answers
193
views
Return back only top N rows for each ID - SQL
I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records
MySQL version: 5.6
SELECT op.*
FROM (select op.*,
(@rn := if(@s = order_id, @...
- 11
1
vote
1
answer
93
views
Mysql: sorting rows of a subquery generated table not working working
I have a subquery (generated by an ORM for pagination purposes) that generates a table that is then sorted by the parent query.
SELECT DISTINCT
`distinctAlias`.`invoice_id` AS 'ids_invoice_id',
...
- 27
0
votes
0
answers
202
views
MySQL Creating Unnecessary Sort Index When an Aggregate Query is a Subquery
I have a complicated aggregate query, using the functions SUM and GROUP_CONCAT, that starts returning results in a reasonable amount of time by itself. But when this query is wrapped in another query, ...
1
vote
1
answer
1k
views
How do I SELECT elements using WHERE with another table's record values in WHERE statement?
Problem summary
Today part of my app crashed, but nothing else stopped with it. I need to find the first and last tracked message ids for each channel during the downtime. I log unix timestamps ...
- 13
0
votes
1
answer
479
views
MYSQL SELECT if else in a subquery with default value in case that a value doesn't exit
I have the next scenario, DB: px
tables:
categories
category_for_level
clients_ip_to_user
full
levels
types
user
I need to check if there is a "url" in the full table which is in one of the ...
- 1
1
vote
1
answer
502
views
Inconsistent results with MySQL, query never ends on MariaDB
We have some really strange behaviour on MySQL (8.0.29) that we can't explain.
We have 2 tables + 1 link table in-between, illustrated by this schema :
We run a query whereby we need to fetch the id ...
- 107
0
votes
0
answers
41
views
Finding duplicate records in a subset
I have 3 tables:
users
orders
order_items
I am trying to get a list of all orders by the same user that have the exact same order_items.item_id within a 24 hour period representing the user having ...
- 399
0
votes
1
answer
424
views
Create a select query to get employee name, total salary of employee, hobby name(comma-separated - you need to use subquery for hobby name)
see the below query table.
Create an “employee” database and 4 tables (hobby, employee, employee_salary, employee_hobby).
hobby: id, name
employee: id, first_name, last_name, age, mobile_number, ...
-2
votes
1
answer
488
views
SQL Subquery not working in Redshift
I'm trying to calculate report for ratio per week. Ratio = Sales_Dollars/Inventory_Dollars
Sales_dollars per week and Inventory_dollars per week are calculated separately in 2 different queries (A, B) ...
0
votes
1
answer
31
views
Improving JOIN with sub-query
I capture Users who comment on the Articles of other Users, as
INSERT IGNORE INTO AuthorCommentators (AuthorID,CommentatorID,Freq)
SELECT AuthorID,CommentatorID,C FROM
(SELECT b.UserID ...
- 4,531
0
votes
2
answers
857
views
MySQL multiple subqueries - slow ordering
Any idea how to optimise this query?
It runs very slow when using the ORDER BY (~10 seconds) clause. If I remove the order_by clause, then it executes for milliseconds.
Problem is, that I need to been ...
0
votes
1
answer
2k
views
SELECTing n rows for each GROUP with WHERE condition
I SELECT n rows for each ArticleID from TagMap table
SELECT a.ArticleID,a.TagID FROM TagMap a
LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
GROUP BY a.ArticleID,a.TagID
...
- 4,531
-1
votes
1
answer
2k
views
Get latest record with max and group by
I am working on a php application and need to retrieve the latest record from a mysql table. Below is the table design and sample data.
So the criteria is:
sort by present DESC,
then start_date DESC
...
- 369
1
vote
1
answer
93
views
Is there anything I can do to optimise this complex query?
I have a rather complex query that spans several layers of subqueries, the two primary ones fetching data from the exact same fields, only one takes the values themselves, the other takes aggregates.
...
- 151
0
votes
1
answer
75
views
Querying both ancestors from a bridging table getting cross join?
Hi I have the following ERM,
kit table
component table
kit_has_substitutes - bridging table
that links a kit to two components, intended to represent the situation where a primary component can be ...
- 9
0
votes
2
answers
90
views
How to UPDATE faster with GROUP BY?
I have a typical article and tag_map tables,
CREATE TABLE `articles` (
`ID` int(11) unsigned NOT NULL,
`Tags` tinyint(3) unsigned DEFAULT NULL,
`Title` varchar(512) DEFAULT NULL,
PRIMARY KEY (`...
- 4,531
2
votes
0
answers
33
views
subquery for history average is slower then expected
I have a MySQL table which contains historic product values. A new record per product is updates every minute. The timestamp field is an INTEGER field with a unix timestamp. There is an index on the ...
- 121
2
votes
2
answers
1k
views
Select maximum of a count in a grouped clause
I have the following tables:
Vehicles(v͟i͟n͟, model,category)
Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date)
vehicleSold(saleID,v͟i͟n͟,salePrice)
When I join these tables using:
select YEAR(Sales....
- 45
0
votes
1
answer
113
views
Trouble with a query subselect
I am trying to merge 2 large queries together and can't get it to work. I have an almost working query, except I can't get the server to accept the p.products_id in the nested query. Can anyone help ...
- 1
1
vote
0
answers
131
views
Ranking and use of cube by in an SQL query? [closed]
I am a the MSc student who does not understand this following SQL dilemma and want to be certain from an Academic standpoint.
The entire “Foodmart” database can be downloaded from: http://didawiki.cli....
- 11
0
votes
2
answers
1k
views
LEFT JOIN subquery fails in the presence of an EXISTS clause in the main query
I have this query:
SELECT c.id contact_id, engagement.sent
FROM contact c
LEFT JOIN (
/* ➊ Inner query */
SELECT eq.contact_id contact_id, COUNT(*) sent
FROM mailing_job j
INNER JOIN ...
- 427
0
votes
1
answer
25
views
Having problems joining table
I have the following tables and I want to know Jack's dob, salary and the total score of that employee who is also a player.
The relational schemas are:
Emp_Players (eid,pid,team) composite key is (...
- 45
0
votes
0
answers
130
views
MySQL - Counting multiple parameters from the same subquery
I have a very large MySQL (~10M rows) database, currently using the InnoDB engine, which stores log events. I am working on a front-end that allows searching through this log database, and one of the ...
- 3
0
votes
1
answer
1k
views
Debit Credit Balance calculating from previous records with Pagination MYSQL
I have table with the following schema and demo data-
id date particulars debit credit
104 29-12-2020 Crediting NULL 1000
105 29-12-2020 Purchased Item:A ...
- 3
0
votes
1
answer
815
views
Sql Group by on subquery rows
I have this table :
ArretProductionJournee(Id, DateArret,HeureDebut,HeureFin,EnumArret)
Example :
DateArret ||HeureDebut ||HeureFin ||EnumArret
2020-11-30 ||2020-11-...
0
votes
1
answer
74
views
How to delete Specific row
http://sqlfiddle.com/#!9/df6245
How to delete all rows Where brand not equal to 107
for example in above fiddle GRPCD 44111 have 107 so it should not delete both rows which have GRPCD 44111 but it ...
- 23
4
votes
3
answers
6k
views
MySQL reuse select aliases
I currently have a query where I'm doing two subqueries to get X, Y data:
SELECT
t.series AS week,
( ... ) X,
( ..., AND ... ) Y,
ROUND(( ... ) * 100) / ( ..., AND ... ), 2) Z
FROM series_tmp ...
-1
votes
1
answer
784
views
MYSQL Error: Incorrect usage of UNION and ORDER BY?
Am trying to randomize my first query but ran into an error "Incorrect usage of UNION and ORDER BY" Kindly find below my queries:
SELECT *
FROM(
SELECT deal_page.id,
...
- 19
0
votes
2
answers
472
views
MySQL subquery with WHERE filters not working as expected
I have a question/tagging system like StackExchange. I want to display the tags associated with each question and also show the total number of times the tag is used, unless the question has been ...
- 101
1
vote
2
answers
537
views
MySQL - GROUP BY not working with multiple CASE WHEN statements
I am working on a database with Kickstarter database, and I'm trying to:
Define short, medium-lengthed, and long campaigns
See how much each length of campaign raise
Convert different currencies into ...
- 11