Questions tagged [join]

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

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

1
2 3 4 5
36