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
54 views

MySQL: SUM() does not provide sum when used with JOIN

This produces one column with 10 numerical rows: SELECT (`sales`.`sale_shipping`) as `total_shipping` FROM `sales` LEFT JOIN `contacts` ON `sales`.`contact_id` = `contacts`.`contact_id` LEFT JOIN `...
user avatar
1 vote
0 answers
129 views

Join JSON column using an index

I have two tables: definition (7,000 rows): +--------------+----------+ | definitionId | nameRef | +--------------+----------+ | 1 | [1] | | 2 | [1,2] | +--------------+-...
user avatar
2 votes
0 answers
95 views

Why does SQL Server 2019 stored proc get Adaptive Join on Development Server, but not on Production Server?

I have a stored proc with one select statement that gets a plan w/ Adaptive Joins on my development server, but not on my production server. What could explain that? I'm not much interested in ...
user avatar
0 votes
1 answer
56 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 ...
user avatar
  • 425
0 votes
0 answers
21 views

Do most real world join conditions involve no more than 2 columns?

In the database application I have worked on, all join conditions are binary, like select * from A join B join C on A.id=B.id and B.orderno = C.orderno Each condition, separated by "and", ...
user avatar
0 votes
1 answer
40 views

Does Greg Rahn's "join order benchmark" reflect real world OLAP join scenarios?

Greg Rahn's "join order benchmark" has dozens of inner join queries on the IMDB database. Does this benchmark and the database itself reflect common real world OLAP join scenarios? How does ...
user avatar
0 votes
1 answer
159 views

Parallel merge join with sorting on large temporary tables in PostgreSQL

I have two tables with text column which I want to join: larger has billion rows and smaller has 100M rows. Tables don't fit into memory, so PostgreSQL reasonably uses merge join for joining. The ...
user avatar
0 votes
1 answer
29 views

MYSQL Joining 3 tables by sequence

I'm gonna try to joining 3 tables by sequence, Table 1 CREATE TABLE table_1(tb1_id int,rpp_id int); INSERT INTO table_1(tb1_id,rpp_id) VALUES ('1','127'); tb1_id rpp_id 1 127 Table 2 CREATE ...
user avatar
  • 3
0 votes
0 answers
33 views

Translation Fallback query

Hello I am trying to use the WordPress database without the WordPress therefore I'm writing some manual queries with Laravel's ORM eloquent, anyways I was trying to implement the fallback translation ...
user avatar
0 votes
0 answers
16 views

Standard solution to join 3 related data-group

i have 3 data-group stuffs - users - notifications each stuff for each user has deferent notification state and i dont know witch model is standard for connect them, I do not want to get into trouble ...
user avatar
0 votes
0 answers
21 views

SQL QUERY Counting Subsequent Inner Join

I am learning SQL for my DAD-220 class and hit a wall on formulating a query. I have tried searching and reading and thrown quite a few queries at MySQL but cannot figure the correct query. I have 2 ...
user avatar
1 vote
2 answers
262 views

Two OPENJSON, return each array element in separate rows

I have a table where I save in JSON format the quantity that exit and return of a product. The data saved in the two columns out_quantity and in_quantity is in array format. CREATE TABLE products_info ...
user avatar
  • 13
1 vote
1 answer
171 views

Unique array values for this string_to_array

This is a follow-up to: Best way to map different JSON keys to same target columns Based on these sample tables: data_providers: id | field_map -------------- 1 | {"segments": "...
user avatar
  • 203
3 votes
1 answer
105 views

Why does an index on the join predicate does not improve the performance of a hash join?

Various references, such as join-strategies-and-performance-in-postgresql, say that an index on the join predicate does not improve the performance of a hash join. However, couldn't a B+ tree index ...
user avatar
  • 33
0 votes
1 answer
28 views

Using Indexing When Performing JOINs on Part of a Composite Key

I have a table in my database (hosted on MariaDB) that looks like this (I have given the definition in SQLAlchemy), class Address(Base): __tablename__ = 'address' __table_args__ = {'schema': DB_NAME} ...
user avatar
-2 votes
1 answer
44 views

Print all the row with two or more element in the column

In following table how to print the student number when age occur two or more times, The result should be only column Sno i.e. Result Sno Reason 1 because 18 occur two times 2 because 20 occur ...
user avatar
2 votes
2 answers
262 views

Can you include the columns of two separate tables in the result of a sql query?

I am trying to order the results of a sql query of one table using the column of another table. Is it possible to include this column from a different table in my results? For example, this query uses ...
user avatar
  • 127
3 votes
2 answers
738 views

Use values from one column as column names and sum data from another table

I'm going to do my best to describe this. So I have 3 tables as follows: project id title department_id 1001 Project One 50 1002 Project two 50 metric id name 101 Number of exhibits 102 Number ...
user avatar
-2 votes
2 answers
30 views

Get count of unmapped records when using a mapping table

I have 3 tables as such Product Product ID Product Details 1 ... 2 ... 3 ... Vendor Vendor ID Vendor Details 1 ... 2 ... 3 ... Product Vendors Product ID Vendor ID 1 1 2 1 1 2 2 2 3 2 ...
user avatar
  • 107
1 vote
1 answer
44 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 ...
user avatar
1 vote
1 answer
83 views

How to index a query with many joins

I have this query with this schema that takes ~8s on my laptop and longer on the vm running the service. SELECT DISTINCT `mofs`.* FROM `mofs` INNER JOIN `isotherms` ON `isotherms`.`mof_id` = `...
user avatar
  • 111
-1 votes
1 answer
105 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 ...
user avatar
0 votes
1 answer
51 views

How to join 2 tables and fill non matching entries with a date

A company buys parts from other companies. To not overload their servers, every 5 minutes the price of 1 part is checked to see if a part has changed price. So a table contains the last time a part ...
user avatar
  • 25
0 votes
0 answers
63 views

Left join performing very badly

Consider the following situation, where the two tables below need to be joined. The query is currently performing very badly. There are no indices defined nor are the ids marked as PK. I was thinking ...
user avatar
1 vote
0 answers
17 views

Postgresql -- join syntax 'bug' that actually ran [duplicate]

I noticed I wrote a query like this, the day after I wrote it: SELECT T1.col1, T2.col1, T3.col1 FROM our_schema.table1 T1 INNER JOIN our_schema.table2 T2 LEFT JOIN our_schema.table3 T3 ON (T2....
user avatar
4 votes
2 answers
117 views

SQL join on an edited field

I'm trying to join Table1 and Table2 on the Url fields. However all the Urls in Table2 end with a "/" as per the example below: Table1 ╔═════════════════════╗ ║ Url1 ║ ╠═══════...
user avatar
  • 43
2 votes
1 answer
201 views

Slow query with multiple INNER JOIN (via table)

This is my first question here. I have a project written in PHP running on windows server and apache and mysql 8. There is functionality to show news by tags (many-to many). So I have three InnoDB ...
user avatar
0 votes
1 answer
33 views

Getting the last few rows of a large table with query that is using JOINs

I'd like to get the last (or last few) rows of a table (named CONTENT) containing more than 10M rows. The query contains joins on 2 other tables and it is extremely slow. These are the table ...
user avatar
  • 233
0 votes
1 answer
133 views

Convert right side of join of many to many into array of objects

I have 2 tables: Table subjects: id, class_id, title, created_at, updated_at Table marks: id, student_id, class_id, mark, created_at, updated_at, subject_id I want to query subjects table and have it ...
user avatar
  • 3
-1 votes
2 answers
125 views

I want to select many columns from many tables without foreign key

I have three tables, and I want to fetch the three table data without a foreign key. Say I have a table product_1 which contains (id, name) and table product_2 contain (id, name, price). I want to ...
user avatar
0 votes
1 answer
166 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 ...
user avatar
  • 5
2 votes
1 answer
50 views

Limiting the number of records from nested INNER JOIN table

The context is as follows: We have 3 tables: We'll call them A,B and C. A has a foreign key referencing table B, we'll call it "bId". The relationship is a ManyToOne. (One record in B can ...
user avatar
0 votes
2 answers
207 views

How is the SQL Query process in multiple Joins?

I have a query like this: SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.ID = table_2.ID INNER JOIN table_3 ON table_1.ID = table_3.ID How SQL process this joins? Does ...
user avatar
0 votes
2 answers
31 views

Why is field in JOIN query null?

I have three tables in a MySQL (Percona) server: What I'm trying to do is find the top albums whose photos have won the most battles. For each album, I want to display a thumbnail of its featured ...
user avatar
1 vote
1 answer
32 views

Does `USING (column)` provide any memory advantage over an an explicit JOIN condition with ON?

Are there any performance differences between JOIN bar USING (foo_id) JOIN bar ON (foo.foo_id = bar.foo_id) I ask this because at least in theory there is more data to project (a duplicate column). ...
user avatar
  • 56.7k
0 votes
1 answer
44 views

UPDATE a table by self JOIN and GROUP BY

I have a table of team players in which the PlayerID is missing in some rows. CREATE TABLE TeamPlayers ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, TeamID int(11) unsigned NOT NULL, PlayerID int(11) ...
user avatar
  • 4,261
0 votes
1 answer
103 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 ...
user avatar
  • 351
0 votes
2 answers
601 views

Is there such a thing as a LEFT CROSS JOIN?

Is there a way to do what I want to call a "LEFT CROSS JOIN"? I.e., is there a way to specify a CROSS JOIN ON, but return at least one row in the result for every row in the left, using ...
user avatar
0 votes
0 answers
28 views

Faster JOIN using LIKE

I have a simple tag table as CREATE TABLE Tags ( TagID int(11) unsigned NOT NULL AUTO_INCREMENT, Tag varchar(255), ParentID int(11) unsigned, Parent enum('Yes', 'No'), UNIQUE INDEX(Tag), INDEX(...
user avatar
  • 4,261
0 votes
2 answers
85 views

Extremely slow JOIN query

We're trying to join tables using this query, however, it is extremely slow to the extend which is not possible to apply to production. Appreciate if we could get assistance at improving the query. ...
user avatar
  • 153
0 votes
2 answers
147 views

UNION SQL with LEFT JOIN

I was trying to do the UNION with two Queries and i received 42601 error. Can you help me to undersant how to fixed it? CODE : SELECT Date_part('year', pr.data_chiusura) AS anno, ...
user avatar
0 votes
2 answers
71 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 ...
user avatar
0 votes
1 answer
30 views

Multiple Joins in a Single SQL Command

I am working with the R programming language. I am trying to perform an INNER JOIN if Condition 1 OR Condition 2 is met: Condition_1 if table_1$id = table_2$id AND if table_1$date BETWEEN(table_2$...
user avatar
0 votes
2 answers
251 views

Do the Number of Columns "Slow Down" the Time Required for SQL Joins?

I am running the following SQL query using the R programming language to perform an inner join: table_1 = data.frame(id = c("123", "123", "125", "125"), id2 = c(...
user avatar
1 vote
3 answers
78 views

JOINing tables when PK and FK differ

I'm new to SQL so this might be easy. I'm trying to join two tables where the first has a standard incremented primary key, but the second table has a column with values like "Ref:36736", ...
user avatar
  • 13
0 votes
1 answer
32 views

Require assistance to write Query joining 3 tables

I have a table called form_submission_entries which contains data like this: id submission_id field_id value 1 1 1 John 2 1 2 Doe 3 1 3 27 4 2 1 Jane 5 2 2 Doe 6 2 3 23 The field_id ...
user avatar
  • 115
0 votes
1 answer
95 views

FInd minimum and maximum number of tuples in R U πA,B ( R⋈S )

Given two relations R(A,B) with r tuples, S(B,C) with s tuples (r>0, s>0), find the min and max number of tuples in this relation: R U πA,B ( R⋈S ) Given options are: -min:r, max:r+s -min:0, ...
user avatar
  • 13
0 votes
1 answer
61 views

Why the JOIN doesn't use index?

I have a query like this: EXPLAIN SELECT dictionary.id, dictionary.word, lwd.grammatical_role, lwd.phonetics FROM loghatnameh_dehkhoda dictionary LEFT JOIN loghatnameh_words_details lwd ON lwd.word = ...
user avatar
  • 187
0 votes
1 answer
64 views

Merging two XML variables - please help!

I am using SQL Server Management Studio 17.9.1 with SQL Server 2016 Service Pack 2 (SP2). There is no table structure to show, just two XML variables that contain XML, as shown below: DECLARE @A XML = ...
user avatar
0 votes
1 answer
28 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 ...
user avatar
  • 4,261

1
2
3 4 5
38