Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [join]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
295 views

UPDATE table vs JOIN performance

I currently have a query with roughly 10 JOIN statements that takes about 10 seconds to run. I have this displayed as a report on SSRS. Would it be better to create a table in SQL server and run ...
6 votes
2 answers
16k views

Understanding multiple table join with aggregation

I have a basic question on how JOIN works on multiple tables. I want to count occurrences of Foreign Key in link1 & link2 CREATE TABLE main ( id SERIAL PRIMARY KEY, name text NOT NULL ); ...
44 votes
2 answers
122k views

Outer Apply vs Left Join Performance

I am Using SQL SERVER 2008 R2 I just came across APPLY in SQL and loved how it solves query problems for so many cases, Many of the tables I was using 2 left join to get the result, I was able to ...
0 votes
1 answer
22 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 ...
0 votes
1 answer
296 views

Querying an enormous table (~150 million rows) with multiple joins - SQL Server

First off, I don't have SHOWPLAN permission on this server or permission to add indexes, etc. I am not a database administrator by trade; I'm just trying to query a database. There is already indexes ...
0 votes
2 answers
762 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
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
29 views

join two table by closest date

I have 2 tables apply_record log_record How can I left join apply_record to log_record on the right closest date. for example final
0 votes
1 answer
349 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
1 answer
632 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
313 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 ...
2 votes
2 answers
19k views

Combining GROUP BY and JOIN [closed]

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 ...
-1 votes
0 answers
19 views

mysql quer to get ten parent comments and child replies for each parent comments

here is my table i want to get parent comments with ten child replies for each parent comment Comment_id Comment_details Parent_comment_id Post_id 1 Aaaaaaaaa 0 1 2 Bbbbbbbb 0 1 3 Ccccccccccc 0 1 ...
1 vote
2 answers
438 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 ...
1 vote
0 answers
21 views

Puzzling performance of join with very little rows in output. Why a bunch of seq scans are so much faster

A lot of things puzzle me in following two plans. The queries should be semantically equivalent. Join with 'pre-filtering' forced. select * from (select * from call_archive where i1 in (SELECT i1 ...
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 votes
2 answers
2k views

SQL Server INNER JOIN multi-part identifier could not be bound [closed]

I have the following statement where selecting 2 tables from a different server and doing inner join but getting multi-part identifier could not be bound. select * from [SQ05].[mov].[dbo].[...
0 votes
2 answers
825 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 ...
0 votes
1 answer
16 views

How to Avoid Duplicates with multiple joins and multiple case statements

I have a query which has multiple joins and need case statements for multiple columns to get the correct data. Although I can manage to get the data, it creates a lot of duplicate rows. Seems like 1 ...
2 votes
1 answer
540 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 ...
2 votes
3 answers
457 views

Why does SQL Server require a LEFT JOIN to retrieve a status that doesn't exist?

I was recently querying our internal database inventory tool for a list of servers, instances and databases, and was adding the corresponding status to each server, instance and database. Relationship ...
1 vote
2 answers
756 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 ...
1 vote
1 answer
288 views

Getting the latest timestamp in Oracle

I am trying to create a report that has the latest member id in the first column and the old member id in the second column based on the timestamp in audits table below. Member ids are stored in ...
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
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....
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 ...
0 votes
1 answer
468 views

Joining without a unique identifier

Payments PaymentRef Product PaymentAmount MerchantRef P01 ABC 100 MR01 P02 ABC 200 MR02 P03 XYZ 200 MR03 Refunds ReundRef Product PaymentAmount MerchantRef R01 ABC -10 MR04 R02 ABC -100 MR05 ...
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 ...
0 votes
1 answer
2k views

Trying to join 2 tables to get a count of orders and grouped by email

I have two tables in DB Orders with columns Orders Id, Orders NO, Customer ID; Customer with columns Customer ID, Customer email. I need a count of the orders for each customer and group by email ...
1 vote
1 answer
315 views

Count nested joined table records only if the table associated via bridge table have two records with given names

I have the main table on which I have to apply count. I have two bridge tables and two nested tables for has_many relation. You can generate tables and bridge tables with below code and also insert ...
0 votes
1 answer
1k views

What is the best way to handle a join / group_concat query in MySQL

I am looking for some feedback regarding the best way to handle the following (simplified for this example) situation. I have 3 tables: tasks, assignees, and users ---tasks---- id, task, completed ---...
0 votes
1 answer
373 views

Simple Update Join much slower than it should be (MYSQL)

This is a simple Update Join that updates only about 100 rows: Update A INNER JOIN B using(id) SET A.active = 1 WHERE A.date > '2020' This takes about 30 seconds to run, despite the fact that: ...
0 votes
3 answers
478 views

Get the ID of a next greater/less Value

imagine you have follwing table given id value 1 5 2 3 3 7 4 1 Depends on your case you need the ID of the next smaller or greater value. So the select of ID=2 would give u either 4 or 1. How ...
1 vote
2 answers
342 views

What should I index when joining on a table with conditions?

I have a query like this: SELECT "main_table".* FROM "main_table" INNER JOIN "other_table" ON "other_table"."deleted_at" IS NULL AND "other_table"."id" = "main_table"."appointment_id" WHERE "...
-1 votes
1 answer
292 views

MySQL multiple table joins and multiple data levels - best practice

I have multiple tables i wish to join up to produce one big query with specific columns summed. I was having a problem getting the correct summed columns in the final query (i believe this was ...
0 votes
2 answers
2k views

Does JOIN condition work like a loop on the joined table?

Do JOIN conditions work like a loop? (like Foreach) and if so, does it run the loop on the first table (the one after SELECT) or the second table (the one after JOIN clause) ? Let's say you have the ...
0 votes
2 answers
4k views

Confusion over using LEFT JOIN on multiple tables

I may be misunderstanding something basic here, but I'm struggling to find this issue being explained in my research. Let's say we have a users table and other tables that relate to the user, let's ...
2 votes
2 answers
13k views

SELECT only IS NOT NULL columns with LEFT JOIN

I have a query where i need to select product options from multi tables. SELECT p.option_id, p.product_id, p.sku, p.qty, p.price, c.color_label, s.size_label, m.material_label, v.variation_label ...
1 vote
2 answers
2k views

MySQL: Data pagination according data on another table

I have a database with the following structure Items table: itemID, itemName Ads table: AdsId, itemID I would like to make pagination on items table, 10 items in each page, but first I must retrieve ...
0 votes
1 answer
395 views

Poor performance on MySQL 5.7 on joined tables ( scanning wrong table ?)

I have a joined table (News, Publishers) with indexing. The query is working fine on MySQL 5.5. After I upgrading one of the server to MySQL 5.7, I start noticing high load, high CPU, and slow query. ...
1 vote
1 answer
631 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
11 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
30 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
555 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
517 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
936 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
49 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
711 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
84 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:...

1
2 3 4 5
41