All Questions
109 questions
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
0
votes
1
answer
33
views
How to Do a Sub Query and JOIN for Comparison Report
Hi I was trying to do a Sub Query and Comparison Report
For Example, I have a Table of Teams with Color AND Name in a Single Table.
A person can be on more than one Team and Multiple Team Colors can ...
- 101
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
-3
votes
1
answer
130
views
subquery vs join which is better [closed]
SELECT *
FROM
table1
WHERE
t2id in
( SELECT id FROM table2
WHERE t3id =
(SELECT id FROM table3 WHERE id = '1') );
SELECT * ...
- 97
1
vote
1
answer
162
views
Combining Multiple queries from the same table inrto 1 query
I am looking to find a way to achieve the below...
I have tried using Joins and SubQueries but no joy as yet...
I have a table that keeps track of wins/loses/draws as so
Date
WLD
01/01/23
Win
05/01/...
- 43
0
votes
2
answers
57
views
I have a database with a player, tournament, and registration table. I need to query all players that played in 2 types (attributes) of tournaments
I currently have two tournaments that have matches. Wimbledon and Atlanta open which I have one as a grand slam and the other as a masters 1000, and I want to get the players who played in both
This ...
-2
votes
1
answer
24
views
Extracting data from two tables
I have( for example) a table called A and a table called B.
Table A contains tons of user data( age, country bla bla) and is connected to B by forein_key=id( B only has ID/NAME.
I want to find out how ...
- 1
0
votes
2
answers
2k
views
How to Select The Lowest Date in one field that's higher than the date in another field
The below aim isn't actually what my query is for, but I'm using it as an analogy to explain more simply what I am trying to achieve:
I am trying to build a BigQuery Script which looks at all arcade ...
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
votes
1
answer
2k
views
Optimize 5 million records SQL query (use join or not?!)
I have a table for products that contains almost 5 million records.
I have a column for the product category (product_category). That it is for now of type INT(11) and it is an index and refers to ...
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
1
vote
0
answers
457
views
Postgres: one-to-one join with duplicates and inequality matching
** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 **
I have two tables taken from two data sources,...
- 11
1
vote
1
answer
2k
views
How do I get the sum of a field and avoid duplication from an inner join?
I am pulling Geo from the Accounts table and I am ranking the Geos by Opportunity $$ Amt based on Accounts that have the name like "Brinks%".
Accounts are unique, and there can be many ...
- 13
-1
votes
1
answer
4k
views
SQL query to join on maximum value on a per-row basis
I'm stuck writing a query for the following scenario: I have a table of things, they have a metric called number in the examples below. I have another table called offers which is a list of offers ...
0
votes
1
answer
770
views
Understanding why getting each parent row joined with its latest child is not working when using subquery filter
I have a parent-children tables relationship, and I was trying to get each parent row with its latest child row details.
My attempts to do that using a sub-query didn't work, and I could only made ...
- 457
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
-2
votes
1
answer
598
views
Having trouble with where subquery count
Assume all of the table and field names are correct. I've tried this a few different ways but this is the only that makes sense needing the join in both. This is the question:
SQL query to get the ...
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
0
votes
2
answers
73
views
Independent aggregation of related entities, while also sorting by related (in a single statement)
I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named ...
- 101
7
votes
2
answers
10k
views
Does Postgres optimize this JOIN with subquery?
In Postgres 12, I have a table purchase_orders and one for its items. I'm running a query that returns PO's for a given shop and a sum of items ordered on each PO:
SELECT po.id,
SUM(...
- 73
1
vote
1
answer
38
views
How to JOIN a table with itself while using a sub-query?
I JOIN a table to itself as
SELECT t1.TagID AS TagID1
,t2.TagID AS TagID2
,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY ...
- 4,531
3
votes
1
answer
20k
views
ERROR: subquery must return only one column
I have two tables one is Category and other one is Product.
Table Description are
Category
name
description
parent_id (self referencing key) (can be upto 3 levels)
Product
name
description
type
...
- 133
0
votes
0
answers
69
views
I'm having trouble generating a list of downloadable items (data from three tables) with a column showing most recent download time for each
I am using the following query to generate a comprehensive download log with data from multiple tables (reduced to three for this example):
SELECT wp_download_log.ID
,wp_download_log....
- 23
0
votes
2
answers
308
views
Inherit INNER JOIN fields in MySql SELECT QUERY
I want to know how I can join one field of an INNER JOIN table in a SELECT QUERY.
Here's what i got so far:
In this query my result would be NameA, NameB and idC, but i want to join tableA and tableC ...
- 15
0
votes
4
answers
102
views
How to visualize joins between inner queries to their respective outer queries?
I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query.
Here's an example:
SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
WHERE ...
- 39.5k
0
votes
1
answer
635
views
How to show multiple record if has top 1
Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-...
- 29
1
vote
1
answer
30
views
Assistance with MySQL query
I have following tables
Tags
id | tag_name | slug |
1 | tag1 |tag1
2 | tag1 |tag1
products
id | proudct_name
1|product1
2|product2
product_tags
id | product_id | tag_id
1|1||1
2|1|2
3|2|1
i ...
- 113
8
votes
1
answer
4k
views
Replace long GROUP BY list with a subquery
This is a repost of my question on Stack Overflow. They suggested to ask it here:
I found an online article from 2005, where the author claims, that many devs use GROUP BY wrong, and that you should ...
- 193
0
votes
1
answer
58
views
How to rank an aggregation in each newly formed group?
The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL.
Let's say I have a simplified ERD that looks as follows:
This ...
- 101
0
votes
1
answer
199
views
Problem with How to join with subquery
I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get ...
- 111
0
votes
1
answer
48
views
Why last table don't using index for joining?
By the help of others members i get an Query idea for my previous problem. But after writing this, the EXPLAIN showing that my last table do't using index for join but the joining ROW is PRIMARY KEY, ...
- 101
0
votes
1
answer
145
views
Cannot get a user function to co-operate with a subquery in mariadb
The following is a demo of an issue we are having with a function and a subquery. I'm interested to know if it is a bug or if there is a reasonable explaination why it is failing.
Notes:
Problem ...
- 111
1
vote
1
answer
118
views
Why Output is wrong after doing the left join as subquery
I have TWO QUERY which work perfect, but when i join those two query as LEFT JOIN the output become wrong. So what is the problem.?
Query 1:
select Batsman.innings_no,
bowler,
sum(...
- 101
0
votes
0
answers
45
views
Comparing count of 2 tables, is this the correct way?
I'm building a survey system where
A client that has many users
A user can have multiple surveys.
I have a table users and a table survey_completions, I'm wondering if this is the correct (or best) ...
- 169
1
vote
3
answers
4k
views
How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql
I have legacy tables similar to the following:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 ...
- 111
0
votes
1
answer
756
views
How to optimize UPDATE with a nested SELECT subquery?
I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do:
In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0
votes
1
answer
45
views
Which query execution will be faster
In the following queries, which query execution will be faster. When I tried executing them in SQLWorkbench, each time query execution time is varying.
Also, can you guys suggest some better tools ...
0
votes
1
answer
7k
views
How to join 3 tables in MySQL
I’m looking at 3 tables here with:
1 called Users containing a UserID.
UserID is linked by a foreign key to UserID in a table called Orders.
Within Orders there’s a PassengerID linked by a foreign ...
- 11
-2
votes
1
answer
59
views
Subquery and subselection for Count/Number doesnt work
for example, for company (3) and his partners, I need the number of events with payouts. But i get only the number for one partner, im my example i have
i have 3 tables (t_company, t_partner, t_event,...
- 1
1
vote
2
answers
1k
views
How to use GROUP BY in an efficient way without losing attributes? [closed]
I am working with PostgreSQL.
My idea is to group players who appear more than 3 times in table-a.
table-a = <player_id, year_world, c, d, e>
With the first subquery I get player_id and the ...
- 109
-1
votes
1
answer
4k
views
Replace Subquery with JOIN - MYSQL
Can you please suggest how i can replace subquery in fetching select columns to improve the performance of query :
SELECT sa.id AS patAppIdSA,
sa.rte_id AS patRTEId,
sa.case_type_id AS ...
- 109
1
vote
3
answers
288
views
MYSQL Update data based on top 10 records of each user
I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of ...
- 391
1
vote
1
answer
27
views
Join or Replace colum value based on query
I have this query:
SELECT h.name, i.name, i.delay, i.delay_flex, i.status, i.templateid, i.params, i.description
FROM items as i
LEFT JOIN hosts as h
ON i.hostid = h.hostid
AND i.type = 11
AND i....
- 25
0
votes
1
answer
855
views
unable to add limit to the subquery
its my first question, please bear me.
I have following tables
Item
id
name
description
itemPurchaseDetail
id
item_id(FK)
branch_id(FK)
purchaseRate
date
Stock
id
item_id(FK)
branch_id(FK)
...
- 15
1
vote
3
answers
12k
views
Joining two tables by the nearest match
General problem: I want to do a left join on two huge tables, but there is no matching key - I want to join the left table with the "nearest" row in the right table. In this example I want to join ...
- 211
3
votes
1
answer
2k
views
Reuse results from correlated subqueries in same query
I have 4 tables (I actually have lots more that I want to do this with...but this is where I am starting).
distr_catalogs: has many distr_catalog_brands, distr_catalog_system_types
...
- 33
1
vote
2
answers
156
views
Possible to rewrite this query as a join?
I'm fairly certain it's possible to rewrite this as a join query but I'm just not sure how to approach it:
SELECT
events.*,
(SELECT MAX(updated_at)
FROM events e
WHERE e.user_id =...
- 113
3
votes
2
answers
2k
views
Join is causing other column data to change?
I'll lead off with saying that I am relatively new to databases and SQL and I am trying to make an addition to a View in SQL Server that a previous co-worker created.
I am trying to join the ...
- 133
2
votes
1
answer
13k
views
Filtering subquery results with group by
I have a join that works but seems hacky because it orders the subquery and uses group by to filter everything but the top subquery result. Without doing so, the cartesian product of s and ...
- 23
0
votes
1
answer
336
views
Select one row from n number table; Join / Union table with looping
I have a list table.
locations_list
id | location | table
----------------------
1 | Location 1 | location_1
2 | Location 2 | location_2
3 | Location 3 | location_3
location_1
...
- 113