Questions tagged [join]

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

Filter by
Sorted by
Tagged with
2
votes
1answer
37 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 ...
0
votes
2answers
60 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 ...
0
votes
2answers
27 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 ...
1
vote
1answer
28 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). ...
-1
votes
0answers
30 views

Left Join in Postgres Rule [closed]

I am working with an INSTEAD OF insert rule in Postgres. I am trying to get a left join in the rule, but the challenge is it doesn't appear I can directly reference the NEW psuedo-relation table. ...
0
votes
1answer
30 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) ...
0
votes
1answer
28 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 ...
0
votes
2answers
52 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 ...
0
votes
0answers
22 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(...
0
votes
1answer
24 views

Automatically Handling Duplicate Column Names (Netezza)

I am using Netezza SQL. I was able to successfully run the following query: select a.*, b.* from table_a a inner join table_b b on (a.date_1 between b.date_2 and b.date_3) and (le_dst(a.id1, b.id1) = ...
0
votes
2answers
30 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. ...
0
votes
2answers
38 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, ...
0
votes
2answers
45 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 ...
0
votes
0answers
17 views

"Fuzzy Joins" supported by Netezza

Are there any "Fuzzy Joins" functions supported by Netezza? Using the R programming language, I have the following style of SQL query I would like to run over Netezza: library(RODBC) library(...
0
votes
1answer
22 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$...
0
votes
2answers
20 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(...
1
vote
3answers
53 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", ...
0
votes
1answer
26 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 ...
0
votes
1answer
47 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, ...
0
votes
1answer
43 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 = ...
0
votes
1answer
38 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 = ...
0
votes
1answer
24 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 ...
0
votes
1answer
39 views

Select Distinct with Inner Join Bringing duplicated Values

I am having trouble using inner join to bring unique values from two tables. SELECT distinct cast (opd.order_number as integer), p.payment_id , pt.transaction_status, opd.installments_number, ...
0
votes
2answers
53 views

How do I select only records which are referenced by their children in other tables?

Not sure if the question is phrased correctly but here's an example of the schema: CREATE TABLE TabA (Id INT NOT NULL, PRIMARY KEY (Id)); CREATE TABLE TabB ( Id INT NOT NULL, TabAId INT, ...
0
votes
0answers
30 views

Huge overhead of multiple Index JOINs over a single row

Consider the following type of query, which has many joins: select t0.*, t1.*, etc.. from t0 left join t1 on t0.t1_id = t1.id left join t2 on t0.t2_id = t2.id etc.. left join t11 on t0.t11_id = t11.id ...
0
votes
0answers
43 views

aggregation with group by, inner join and nested conditions in MongoDB

First of all I am sorry if this is a basic question, I am new to queries in MongoDB. Well, what I need is to find the latest registers for a worker in my WorkerLocationContext document and the latest ...
0
votes
2answers
76 views

Which is the correct JOIN to use in this case?

I have three tables named exam, student and subject. You can learn more about the data by checking this link: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b80b08d0517678b8da3b70d489462d4f I want to ...
-2
votes
1answer
31 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 ...
1
vote
0answers
26 views

Filling gaps in result with empty data

PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit Hello All, I'm a bit stuck on this one. Working SQL Currently I am using this query: ...
2
votes
2answers
35 views

Silly beginners question about joins

I have no previous experience of SQL but I just now have an assignment where I'm supposed to join 2 tables using different kinds of joins (on physical paper, not in an editor). Table 1 is called ...
0
votes
1answer
155 views

Strange Error ... Conversion failed when converting the varchar value 'www.com' to data type int

The issue that I am running into is happening on Microsoft SQL Server. I have a view that is unioning together 2 tables. It is returning a little over 7 million records. When I attempt to JOIN ...
0
votes
1answer
68 views

Slow query Mysql with Order By and JOIN

I've been investigating an issue with a slow query when trying to get the last 50 comments of a user. To give some context the database size is 9 TiB and the COMMENT table has about 7 billon of rows. ...
0
votes
0answers
18 views

MySQL LEFT JOIN, returning data for 0/1 values vs. NULL values [duplicate]

Have this MySQL SELECT query: SELECT product.id, product.name, shop.currency, price.amount, shop.name FROM product LEFT JOIN price ON product.id = price.product_id LEFT JOIN shop ON price.shop_id = ...
2
votes
2answers
1k views

Join condition vs. WHERE...does it make a performance difference?

I've developed a habit of separating join conditions from other additional conditions. However I understand the logical execution order is: FROM WHERE Can it be detrimental to performance if I add ...
0
votes
0answers
31 views

tsql do update queries that join other tables lock the joined tables also?

Azure SQL Managed Instance. If I am updating a temp table, and joining to another table or tables, do the joined tables also get locked, even though the update statement is only updating the temp ...
0
votes
2answers
51 views

Improving query (Doing a nasty self join on a range with ctes)

-- The CTE1 is just to create columns that dictate the bound of what is considered the same entry -- Also I do a dense rank by ACT_TIME, and a PARITION BY on ID1, ID2 -- so all ID1/ID2 combos are ...
1
vote
1answer
39 views

Is there any way to join the same two tables many times easily?

I have two tables named "Player" and "Match". In "Player", There are two columns: Player.id and Player.name and in the "Match" table I have many columns related ...
0
votes
2answers
55 views

Skip one row inside request using inner join (sql compact server 4.0)

I am using SQL server compact 4.0 and VB.net and Visual studio 2019. I am using this request to extract information from 4 tables : Table0, Table1, Table2 and Table3. This request is working fine ...
0
votes
0answers
34 views

how to pass outer field to join subquery in Postgres?

I have two tables create table if not exists orders( order_id Integer, order_time Timestamp ); create table if not exists balance_events( event_time Timestamp, amount Integer ); For each order I ...
-1
votes
1answer
62 views

How to pass a parameter into a subquery in a join statement [closed]

I have a query that should be implemented in an MSSQL View so no local variables allowed, this query is working but it has to call a function to return a temporary table, this function is called ...
0
votes
0answers
47 views

JOIN vs WITH differences

apologize if there is the same question. When I started my career, my manager told me that you don't need to use "WITH", you can do anything with "JOIN". Now I am wondering is it ...
0
votes
1answer
32 views

A table that has two foreign keys to the same column in another table?

I need to create a database structure for a 1v1 game. This is what I have designed so far. Now here is the problem: I want to display all the data of matches on the screen, but instead of the foreign ...
0
votes
1answer
27 views

How do you JOIN two tables as a combination of LEFT and RIGHT JOINs?

This is probably a stupid question. In two tables of CREATE TABLE t1 ( id int(11) unsigned NOT NULL, col1 int(11) unsigned NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB; CREATE TABLE t2 ( id int(11) ...
0
votes
1answer
27 views

Get inserted values / UNIQUE key over multiple tables / MySQL

Caused by some bad design decisions, I have to make sure that a random-character string (no sequence!) must not appear twice in three different MySQL tables (TableA, TableB, TableC). So if the value &...
2
votes
0answers
130 views

How to determine the Tipping Point of which Physical Join Operator gets used to process a query?

I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
0
votes
0answers
16 views

mysql join with filtering multiple partial payments to the date

I have two tables: table "documents" id | date | money 1 | 20.01.2020 | 100 (this is a bill) 2 | 25.01.2020 | 25 (it is a payment) 3 | 30....
1
vote
1answer
56 views

PostgreSQL Query Optimization - Joining with a lookup table does not use index

I am attempting to optimize indexes and querying for a postgres database, and have run into some issues around a lookup table I have used to support joins for a many:many relationship. Here is the ...
0
votes
1answer
40 views

Why does mysql join strategy depend on this value?

I have a simple select, involving a join: SELECT `user`.*, `group`.`alias` AS `group_alias` FROM `user` INNER JOIN `group` ON `user`.`group_id` = `group`.`id` WHERE `user`.`state` = 1 AND `group`...
0
votes
1answer
33 views

Get data from 2 tables based on comma separated values Join

I am an average in database operation but I never deal with a database having values in comma separated manner. As I want the result from both table. I have one table "Form table" +------+---...
1
vote
1answer
75 views

need help with joining the 2 tables on closest date

I am a newbie in SQL and need help with the joining of these 2 tables on the closest date. Thanks in Advance! Table A billing_start_date id status 2020-07-27 100 active 2018-12-27 101 inactive ...

1
2 3 4 5
37