Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,999
questions
1
vote
1
answer
606
views
Simple query with a single join very slow
I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
0
votes
0
answers
7
views
SQL: Perform join on multiple tables on Google BigQuery
Please, I am running a professional course in data analytics. Right now, I am on my Capstone project. But I am having challenge combining tables in BigQuery using SQL. The data is about bicycle trips ...
0
votes
0
answers
13
views
Performance Differences in JOIN query Between MySQL 5.7 and MySQL 8.0
Hello MySQL Community,
I wanted to share some observations regarding the performance differences between MySQL 5.7 and MySQL 8.0, specifically related to join algorithms.
In a recent project, I ...
2
votes
1
answer
479
views
Delete using join or using sub-query?
I need to delete rows from a table based on what is present in a temporary table.
For me, both of these statements work :
DELETE from main_table where `id` in (select `deletable_id` from ...
0
votes
2
answers
511
views
MySQL - Many to Many relationship, or one to many with additional columns
Our product contains some constraints that made us contemplate which table design will yield the best performance.
Our model:
Question table which contains fields like id, body, difficulty, and tags.
...
0
votes
2
answers
1k
views
UNION or SELF JOIN?
I have a table to hold parent and child posts together.
+------+----------------+--------------+-------------+------------+
| p_id | parent_post_id | child_status | post_status | post_title |
...
0
votes
1
answer
901
views
Detect with Left Join duplicates entries and return the first value, also return sum of duplicates entries
¡Hi!, i have a case with two tables than can have a lot of matches entries and the time of fetching increases a lot.
This tables can be
Table A: Employees
-------------------------
| Name | ID | ...
0
votes
1
answer
46
views
Inner join by datetime columns
I'm working in restructure a partinioned table so I have two tables with the same schema, for ensuring the insert process was executed succesfully without change some value i want to validate that the ...
0
votes
1
answer
697
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 ...
1
vote
2
answers
2k
views
Mysql: Create a view with multiple self joins without duplicates in result
Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you.
So i have a table entities_attributes_values where an ...
1
vote
2
answers
54
views
Merging information from multiple rows into one row
I have three tables event, person and event_person_map
event:
id integer PRIMARY KEY
event_name text
event_date date
person:
id integer PRIMARY KEY
full_name text
email_address text
event_person_map:...
0
votes
1
answer
1k
views
MySQL get previous row with inner join
In my query, I need to get the previous row with the current row and everything works fine here.
SELECT *,
(SELECT calendar_id
FROM calendar AS sub
WHERE sub.calendar_id < ...
0
votes
1
answer
542
views
Optimize join without denormalization
I have two tables:
orders(order_id, scan_timestamp) and
order_products(order_product_id, order_id, pick_number, quantity)
and I want to know how many of a particular product (identified by ...
0
votes
1
answer
13
views
Select distinct values from one table and join non matching values from second table to create new rows
I have two tables I'm trying to combine into one. They don't have a matching key besides the ID. I need to look up distinct value in table 1 (Zip) and add every value listed in table 2 (activity). All ...
0
votes
3
answers
336
views
Using JOIN to find duplicates in another table taking too long
Have a MySQL 8 database with the following tables.
people
id | mobile | name
registrations
id | people_id | other_stuff
I am trying to find the mobile number and the number of times it's duplicated ...
0
votes
1
answer
1k
views
Left Join with only one of multiple matches in the right table
I have a database, where every entry may have multiple names, therefore I made a name database. While all names will be displayed otherwhere I need a list of all main entries with one representative ...
20
votes
3
answers
9k
views
Column name naming conventions and best practices
I would like some expert opinion on best practices when it comes to column naming.
The background is that according to Wikipedia, the following syntax,
SELECT ... FROM Employees JOIN Timesheets ...
3
votes
2
answers
920
views
Grouping values into buckets
I'm working a query and I wasn't sure if my current approach is the "best" approach.
I got a table filled with values and a table with buckets that I use to group those values.
There are 2 types of ...
1
vote
1
answer
866
views
In Postgresql, can I create a column of a one-dimension array type with foreign key constraint? If not, how to simulate it?
In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be ...
0
votes
1
answer
2k
views
MYSQL join on subquery optimization
This is a distilled version of the query that I am trying to run:
SELECT * FROM table1 t1
INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2
ON t1.id = t2.id;
The above query is incredibly slow ...
0
votes
1
answer
25
views
Try to duplicate column= attribute1 at time=0 horizontally even on rows where time!=0 for every unique ID
Given a table such as this,
I want to create a table via SQL without doing window functions
id
time
attribute1
attribute1atFirstRecordedTimeperID
1
0
a
a
1
2
b
a
2
0
c
c
2
4
d
c
I tried
SELECT
...
1
vote
1
answer
24
views
How do you only get data from the table with the foreign key using SQLite?
I want to be able to get data from the table that owns the foreign key as a primary key, only using data in the WHERE clause from the other table.
I have tried an inner join and I am getting weird ...
14
votes
1
answer
10k
views
Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?
I have the following tables (taken from the Sakila database):
film: film_id is pkey
actor: actor_id is pkey
film_actor: film_id and actor_id are fkeys to film/actor
I am selecting a ...
2
votes
2
answers
1k
views
Complex queries in CouchDB across multiple documents
I'm relatively new to CouchDB (more specifically Cloudant if it matters) and I'm having a hard time wrapping my head around something.
Assume the following (simplified) document examples:
{ "docType"...
1
vote
1
answer
1k
views
SQL Get Users By One Group But Exclude Others
I'm trying to write a query that get's all users that belong to Group A, but excludes users that belong Groups B, C & D.
Users can belong to multiple groups.
I have the following query but it's ...
0
votes
1
answer
4k
views
Costly HASH MATCH Aggregate
I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index)
Here is the view and the plan.
execution plan
CREATE ...
3
votes
1
answer
239
views
Creating a Trigger in MySQL that has a JOIN
PROBLEM:
I'm trying to create a TRIGGER that will copy records from 2 different tables (users, addresses) in database A to a single table (users) in database B.
Basically, I'm trying to consolidate ...
1
vote
0
answers
54
views
Column information from View
Suppose I have this database
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Roll INT NOT NULL,
CustomID INT UNIQUE
);
INSERT INTO Students (StudentID, ...
1
vote
1
answer
4k
views
Index is not used when joining two tables in a stored procedure
In MySQL 5.7, I have two tables:
CREATE TABLE `periodholdings ` (
`stkcode` varchar(255) NOT NULL,
`participantid` varchar(255) DEFAULT NULL,
`participantname` varchar(255) DEFAULT NULL,
`...
0
votes
1
answer
4k
views
How to return missing rows from LEFT JOIN in BigQuery
How can we get BigQuery to return the rows in a LEFT JOIN which exist in TABLE A but are NULL in TABLE B?
-- find missing users. return rows which exist in A but not in B
select a.user_id
...
0
votes
1
answer
54
views
When using JSONb for a complex hierarchy of references, is it better to Join by aggregating the IDs in the JSON, or have additional relations table?
Lets say we have several primary tables in our database (a, b and c), and then another (x) that stores a complex, only semi-predictable JSONb object that has references to all the primary tables. In ...
0
votes
1
answer
379
views
How to Fetch A Record If Shipping And billing Address are Different
Show New Orders with Address If shipping and billing are different. I am stuck with this query. Can someone guide me a bit. Thanks
0
votes
1
answer
601
views
What exactly happens when optimizer_prune_level = 1?
This documentation link states that optimizer_prune_level
Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space.
Can ...
0
votes
1
answer
14
views
MySQL Query duplicates sum for left join
I have 2 tables, Greeter_Detail and ZipCode_Details
I'm trying to SUM the total of People and Families from and Greeter_Detail and join them on ZipCodes, but I get duplicates. ZipCode_Details has city ...
0
votes
1
answer
306
views
MySQL show data from Rows to columns
I am stuck at getting a query with joining 2 tables. Below is the database structure.
What I want to achieve is to list all the information from one table with join on the second table but it has 3 ...
1
vote
1
answer
33
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 ...
2
votes
2
answers
16k
views
Combining GROUP BY and JOIN
This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the ...
0
votes
1
answer
335
views
Joining two select statements
Im using MySql 8.0 and I've been exercising a lot of quries but this one I cant seem to figure it out. I need to join somehow two select statements that I've written and they display the correct ...
0
votes
2
answers
744
views
changing from inner joins to left joins to include null values?
I need to have this query return all NULL Values for hours for each jobcode. I have tried to change the inner joins to left joins, but the output is the same (only showing the 1 jobcode for one date ...
1
vote
2
answers
74
views
Explain plan will sort the result after join even the column included in index
I am using SQL Server 2022 Developer
Trying get all AccessLog that classified to type 1.
SELECT [t].[Time], [u].[UserName], [t].[Type], [t].[Message]
FROM [AccessLog] AS [t]
LEFT JOIN [...
1
vote
2
answers
24
views
How do I join two tables where the result gives me multiple results for some, but I only want one
I have two tables: tng_people and tng_events in my genealogy database.
tng_people stores the basic information about each person, each person having a unique personID.
tng_events stores events that ...
0
votes
1
answer
1k
views
Oracle DB union two select statements which return multiple rows
I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more ...
1
vote
2
answers
429
views
How to avoid conditional join in order to write a tsql view
I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables
Roles table
RoleName
---------
Admin
Viewer
User
Set table
Id Name
--- ----
1 Employee Data
2 Help ...
4
votes
1
answer
141
views
T-SQL: Iteration vs. Set-Based Operations When Independence of Ancillary Data is Needed
BACKGROUND
I am using Microsoft SQL Server Management Studio v18.9.1 on Windows 10.
I've created a database to store test results of our company's widget (not yet being produced). I am working on a ...
0
votes
2
answers
806
views
How do I join a parent table to two child tables without duplication?
parent table
id
city_name
zip_code
1
new york city
10001
2
los angeles
90001
3
chicago
60007
Customer, child table
id
customer_name
city_id
address
1
alice
1
428 Hartford Lane
2
betty
1
249 ...
2
votes
1
answer
499
views
Best practices for large JOINs - Warehouse or External Compute (e.g. Spark)
I am working on a problem that requires a very large join. The JOIN itself is pretty straightforward but the amount of data I am processing is very large. I am wondering for very large JOINs, is there ...
1
vote
2
answers
711
views
Join the SAME tables from DIFFERENT databases
I have an unusual situation where I have a bunch of backups of a mysql database and I am interested in selecting the distinct members of a given table from these backups (since some records might have ...
0
votes
1
answer
7k
views
Need help with left joins to avoid duplicate rows while appending column from another table in MariaDB?
I have this query which is meant to output the guest's name, how much they paid and the description for the associated item they have paid for.
SELECT g.first_name, ex.pmt_method, ex.pmt_date,
ex....
0
votes
2
answers
2k
views
Join multiple tables without duplicated results and without UNION in PostgreSQL
Lets assume we have two tables A and B in PostgreSQL:
A
title
first_group_id
second_group_id
My Title
1
2
B
group_id
element_name
1
First
1
Second
1
Third
2
Fourth
2
Fifth
I want to get the ...
0
votes
1
answer
2k
views
Sql merge columns with same name
I have a table of hockey games and a table of teams.
schedule:
teams:
I am trying to do 2 joins on the same column. I have a schedule of home and away teams in table 'schedule'. I am trying to ...