Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,798
questions
0
votes
1answer
17 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
15 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
49 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
25 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
42 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
38 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
22 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
30 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
52 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
28 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
70 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
23 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
79 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
56 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
17 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
50 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
31 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
40 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
30 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
128 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
14 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
48 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
39 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
43 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
...
-3
votes
1answer
33 views
Must an index cover all columns to be used in a merge? [closed]
For testing purposes, I'm interested in speeding up the following query:
SELECT a.*, b.id
FROM "table" a JOIN "table" b ON
a.id = b.id
where "table" has an index on id. ...
-2
votes
1answer
63 views
Group by on three tables as one master table and two detail tables
I.tblOwnerDetails
Ownerid
ownername
1
Abc
2
Xyz
3
Pqr
II.tblListMaster
Listid
Listname
1
List1
2
List2
III.tblListDetails
Detailid
Listid
Ownerid
1
1
1
2
1
2
3
2
1
4
3
1
IV.tblTagMaster
...
1
vote
1answer
27 views
Find all the artist names with songs in all the possible mediatypes
I have a basic problem about create a query for listing artists with a condition of the artist have songs with all format type, the database have three table ( check ER diagram ).
The Question is: ...
1
vote
0answers
42 views
JOINing two mapping tables
I have two mapping tables as
CREATE TABLE ProjectDevelopers
(
DeveloperID int(11) unsigned NOT NULL,
ProjectID int(11) unsigned NOT NULL,
INDEX(ProjectID),
PRIMARY KEY(DeveloperID,ProjectID)
) ENGINE=...
0
votes
0answers
24 views
Join 2 tables and show row data in individual columns
I got a peculiar requirement to create a SQL query joining 2 tables as below
customer is the first table. This table holds the primary (p) and other (o) customers. The field cust_id creates the link ...
0
votes
1answer
51 views
Why does this sql join query take much longer using a reference to a row from the first table in the 'on' clause versus using a literal variable?
I have two tables, 'auth' and 'ips', which I'd like to join in a query.
Table 'auth':
column name
data type
Extra
id
int
AI
date
varchar(6)
-
timestamp
varchar(8)
-
result
varchar(8)
-
ip
...
-4
votes
1answer
44 views
How to join two tables in sql server? [closed]
I need some help i have a query, struggling to use join and database is sql server. Below is my table definition.
// sql server query
SELECT TOP 1000 [Station Code]
,[Transaction Code]
...
0
votes
1answer
32 views
JOIN only latest row through a pivot table in one to many relation - MySQL [closed]
I have 3 tables and I only need to compare data between 2 of them but only taking in mind the latest row. The structure of the table is as follows
Table Places
id
brand
phone
email
1
Place A
000000
...
0
votes
2answers
37 views
Group_concat with joining multiple tables
I have the following scheme
users: researches: researchyear:
uid | name rid| titleAr rid | uid |
---+-------- ---+------------ -----...
1
vote
2answers
118 views
Can I bring two values from a single row into a Pivot Table
I am trying to get two values from rows in a table into a single row in a pivot table.
myTable has the following rows
ID1
ID2
Item
Value
SP
11
22
Item 1
10
10
11
22
Item 2
100
100
11
22
Item 3
49
...
1
vote
0answers
23 views
List Player Record Types, First Score, and Last Score
I have a record_types and a record table:
CREATE TABLE record_types(
id SERIAL PRIMARY KEY,
record_type VARCHAR(50)
);
INSERT INTO record_types(record_type) VALUES ('dribble');
INSERT INTO ...
0
votes
0answers
18 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
1answer
37 views
How to get Postgres to use a MergeJoin?
I've got two tables with the same type of primary key (and thus sorted on that key, I'm assuming). I want to update rows in one table based on matching / joined rows in the other table. I expect a ...
0
votes
1answer
71 views
MSSQL Join to put rows into columns column variable as column name
I have two tables
TABLE 1
PRODUCTID
TITLE
001
Product1
002
Product2
TABLE 2
ID
PRODUCTID
VARNAME
VARVALUE
1
001
url
https://...
2
001
length
30m
3
001
somethingelse
somevalue
I am trying to ...
