Questions tagged [join]

An SQL join clause combines records from two or more tables or views.

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
40