Questions tagged [join]

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

Filter by
Sorted by
Tagged with
1 vote
2 answers
378 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 ...
John K. N.'s user avatar
  • 17.7k
0 votes
0 answers
7 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 ...
Nduka Charles Jala-Igwe's user avatar
0 votes
0 answers
20 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 ...
Amarjit Singh's user avatar
0 votes
1 answer
46 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 ...
Carolina's user avatar
1 vote
2 answers
61 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:...
Lugoom485's user avatar
0 votes
1 answer
14 views

Select distinct values from one table and join non matching values from second table to create new rows

I have two tables I'm trying to combine into one. They don't have a matching key besides the ID. I need to look up distinct value in table 1 (Zip) and add every value listed in table 2 (activity). All ...
J.R.'s user avatar
  • 5
1 vote
1 answer
26 views

How do you only get data from the table with the foreign key using SQLite?

I want to be able to get data from the table that owns the foreign key as a primary key, only using data in the WHERE clause from the other table. I have tried an inner join and I am getting weird ...
av4625's user avatar
  • 115
0 votes
1 answer
25 views

Try to duplicate column= attribute1 at time=0 horizontally even on rows where time!=0 for every unique ID

Given a table such as this, I want to create a table via SQL without doing window functions id time attribute1 attribute1atFirstRecordedTimeperID 1 0 a a 1 2 b a 2 0 c c 2 4 d c I tried SELECT ...
Coo's user avatar
  • 101
3 votes
1 answer
245 views

Creating a Trigger in MySQL that has a JOIN

PROBLEM: I'm trying to create a TRIGGER that will copy records from 2 different tables (users, addresses) in database A to a single table (users) in database B. Basically, I'm trying to consolidate ...
Tony Stephens's user avatar
1 vote
0 answers
58 views

Column information from View

Suppose I have this database CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(255) NOT NULL, Roll INT NOT NULL, CustomID INT UNIQUE ); INSERT INTO Students (StudentID, ...
Shoaib Ahmed's user avatar
0 votes
1 answer
56 views

When using JSONb for a complex hierarchy of references, is it better to Join by aggregating the IDs in the JSON, or have additional relations table?

Lets say we have several primary tables in our database (a, b and c), and then another (x) that stores a complex, only semi-predictable JSONb object that has references to all the primary tables. In ...
Henry's user avatar
  • 213
0 votes
1 answer
15 views

MySQL Query duplicates sum for left join

I have 2 tables, Greeter_Detail and ZipCode_Details I'm trying to SUM the total of People and Families from and Greeter_Detail and join them on ZipCodes, but I get duplicates. ZipCode_Details has city ...
angel12333's user avatar
1 vote
1 answer
33 views

MySQL Tennis Ladder View Creation Question

I am creating a Tennis Ladder VIEW using MySQL and wish to do the following: Properly create a view that includes: All player's names These player names will come from the table: User - using the ...
Reno Kendo's user avatar
1 vote
2 answers
24 views

How do I join two tables where the result gives me multiple results for some, but I only want one

I have two tables: tng_people and tng_events in my genealogy database. tng_people stores the basic information about each person, each person having a unique personID. tng_events stores events that ...
user1088537's user avatar
1 vote
2 answers
74 views

Explain plan will sort the result after join even the column included in index

I am using SQL Server 2022 Developer Trying get all AccessLog that classified to type 1. SELECT [t].[Time], [u].[UserName], [t].[Type], [t].[Message] FROM [AccessLog] AS [t] LEFT JOIN [...
Uni's user avatar
  • 11
1 vote
1 answer
101 views

How to allow predicate pushing on a view that uses group by

We have a table Ecom.McProductToVendorProductCodeMap that has a multi-field PK as shown: Then a view wraps that table to compute a metric and is grouped by the first two fields of that PK: ALTER view ...
crokusek's user avatar
  • 2,120
0 votes
2 answers
127 views

Duplication records with INNER JOIN

I’m validating records after a partition’s rotation.When I run the SELECT COUNT for each table I get 114,817,959 records. SELECT COUNT(*) FROM SMS.dbo.MessagesPerf WITH (NOLOCK) WHERE DateEntered &...
Carolina's user avatar
-1 votes
1 answer
60 views

Question on joins with multi tables

I have 4 tables ( 1 to many table relation) as shown Main , Worker , Location and MiddleName Table Any suggestions on how to get the result. I tried joins and it never worked.
sunny's user avatar
  • 1
0 votes
1 answer
59 views

SQL Pattern to get "root/ start" of uneven hierarchy dataset

I feel this is a common problem and I've seen it in some SQL challenges even but for the life of me, cannot think clearly about a solution. Say you have an uneven hierarchy. Elements that belong to ...
user45867's user avatar
  • 1,683
0 votes
1 answer
167 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 ...
Bigbob23's user avatar
  • 121
0 votes
0 answers
46 views

Complex day-to-day difference of historical data in SQL server

Setting: I have a table (let's call it "HistoricalData") with 10 attribute columns, a date (in string format) and a value. The table has a unique clustered index on (date, idcol1, idcol2,.....
101001000100001's user avatar
0 votes
2 answers
113 views

How to apply JOIN only if there are rows available

Given the following database: CREATE TABLE "Activity" ( "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY, "designation" TEXT NOT NULL ); CREATE ...
Exprove's user avatar
  • 109
1 vote
1 answer
74 views

Does SQL implementations (or at least SQL Azure) skip obvious duplicate data with joins when returning data?

Say I have: very wide table A where I need all columns that I need to join with a small table B that has a LOT of rows. Of course when I join these two tables I get returned all the content of A for ...
Dirk Boer's user avatar
  • 365
1 vote
1 answer
79 views

Limit results of complex query to most frequent values in given attribute

Say that you have the following tables for a multi store platform CREATE TABLE orders ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, store_id BIGINT NOT NULL, ordered_at TIMESTAMPTZ NOT ...
nbon's user avatar
  • 113
0 votes
1 answer
21 views

I am getting an infinite fetch duration when adding a similar third left join

I am using MySQL on Windows 10. The query below should result in three groups of data sets that are different only in the date range. It works for any two date ranges but when I try to run all three ...
Mathly's user avatar
  • 1
0 votes
0 answers
22 views

SELECT for VIEW with steped date dependencies

I have two tables on a mariadb 13.0 - values: id date value1 value2 1 2023-10-01 10 100 2 2023-10-02 20 200 3 2023-10-03 30 300 4 2023-10-04 40 400 5 2023-10-05 50 500 and rates: date rate1 ...
Wookiee's user avatar
0 votes
0 answers
6 views

Handling clunky join condition (Newbi)

(Newbie question I think) [Not a mission critical issue just for my understanding]. I have a situation where I have two SharePoint tables as follows: Table 1: UserTable: user, Position, Department, ...
Todd Wantz's user avatar
1 vote
0 answers
57 views

Adjust SQL query to select only one record

I have three tables: An adjacency-list table, territories with fields id, alignment_id, three_left, tree_right, depth and deleted_at (omitted from table for brevity, all values null). id alignent_id ...
ioni's user avatar
  • 61
0 votes
2 answers
185 views

Does the JOIN really duplicate data on the level of response from the database?

As an example, let's take PostgreSQL and say we have two tables with one to many relationship. Making the INNER JOIN produces "duplicates" from the first table. Are those duplicated rows ...
crabvk's user avatar
  • 3
1 vote
1 answer
56 views

How can I SELECT columns from one table that corresponds with a column of another table on JOIN without creating a relation between the tables?

I have 3 tables: CREATE TABLE catalog.shoes ( shoe_id SERIAL PRIMARY KEY, brand VARCHAR(255) NOT NULL, model VARCHAR(255) NOT NULL, price INT NOT NULL, CONSTRAINT shoe_name UNIQUE (...
ntruter42's user avatar
  • 121
0 votes
1 answer
36 views

Get Point from Another Table based on Category and Value Range

I am trying to get a column which specify the points by comparing the value from original table to min and max value from another table. I have a main table, which consists of few columns of values ...
John Marston's user avatar
0 votes
1 answer
33 views

Why use $group / group_by instead of multiple requests?

Let's suppose that there are 100 types of dogs scattered across the documents of my collection. If I need to group by types of dogs and then compute some aggregate statistic about each type of dog, ...
Bear Bile Farming is Torture's user avatar
0 votes
1 answer
41 views

Slow join on geometry fields

My situation is somewhat similar to this earlier question: In PostgreSQL I have one table containing point geometries (P), and another containing polygons (G). I would like to join on the geometry ...
Berend's user avatar
  • 121
0 votes
2 answers
512 views

Is it a good idea to join data from two different databases?

I am building an application that requires a lot of tables in one database and while the joining and aggregation of data is really nice and seamless, I am starting to wonder if I am creating too many ...
jaffer_syed's user avatar
0 votes
1 answer
109 views

UPDATE & SELECT & INNER JOIN combined (SQL92) — How?

I have two tables with the following columns: Table1 id Sort dou and Table2 id_Table1 Sort and I need to read all values of Table1.Sort where Table1.dou = 1 and write them into Table2.Sort, into ...
Gary U.U. Unixuser's user avatar
0 votes
0 answers
91 views

Dynamically selecting a table in a query based on result from joined SELECT

I have seen many examples but they don't match with my special case (I know... everyone's case is a special case :D ) I have to match values from different tables, some of which are named after the id ...
jprealini's user avatar
  • 101
2 votes
2 answers
614 views

Ignore duplicate rows in a join - and take only one of them

In MySQL I have tables, simplified as follows: companies (id, name, rank, trade_sector_id) orders (id, company_id, paid, subtotal) trade_sectors (id, label) companies_notes (id, company_id, notes) I ...
Ade's user avatar
  • 123
0 votes
1 answer
126 views

Join 1 row out of multiple 1:1 tables and return as array of json objects

I have a database structure like so (in Postgres): An entity has a list of actions associated with it. Each Action has one associated row out of all action-specific tables. Each of these action types ...
Gmanicus's user avatar
0 votes
1 answer
92 views

Help with SQL Query: Is there a cost-efficient way to join multiple tables to one main table?

I have a main table set up like this: id table_to_join table_item_id 1 products 123 2 products 577 3 products 344 4 products 1230 5 images 14 6 images 42 7 video 555 8 products 400 9 video 9 ...
peppy's user avatar
  • 33
0 votes
2 answers
102 views

Delete all rows if input parameter is 0 otherwise delete only those entries which match the input param

I have a stored proc which is receiving id as input. If the id which is received is 0 then we need to delete all the rows otherwise we need to delete only those rows which matches the input id. I can ...
Himanshuman's user avatar
3 votes
5 answers
2k views

Optimizing SQL Server Query When Joining Tables on DATETIME column

I am working with SQL Server and I have two tables, table1 and table2. Both tables have a DATETIME column denoted as dt. I need to join these tables based not only on certain conditions but also to ...
Fajela Tajkiya's user avatar
2 votes
2 answers
298 views

Column isn't in GROUP BY error on join query

I'm trying to get best selling products. Tables CREATE TABLE `items` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `price` ...
Mohammad A. Apu's user avatar
-3 votes
1 answer
111 views

subquery vs join which is better [closed]

SELECT * FROM table1 WHERE t2id in ( SELECT id FROM table2 WHERE t3id = (SELECT id FROM table3 WHERE id = '1') ); SELECT * ...
guradio's user avatar
  • 97
1 vote
1 answer
74 views

PostgreSQL: Sum filtered items in a one-to-many relation

I have events and sessions associated with them in a one to many relation. I want to make a query to get the amount of past, happening and upcominng events. Past events have no active sessions ...
Tomás Mendes's user avatar
1 vote
2 answers
176 views

How to optimize SELECT COUNT with LEFT JOIN?

I have a MariaDB 10.5.10 database, with a table activity made of 150,000 rows. it holds: an id field (integer, primary key) a tenant. There are about 85,000 rows with tenant = 12 an activity_type (...
bfredo123's user avatar
  • 113
0 votes
1 answer
64 views

How to join matching Date fields as intervals?

Let say I've a table with a list of id/year/month (two years + 24 months for semplicity here): Code Year Month MAT000000001 2022 1 MAT000000001 2022 2 MAT000000001 2022 ...
markzzz's user avatar
  • 243
0 votes
1 answer
37 views

Joining per group in group by

I have a table with some animal sightings data where each sighting entry has a region field. There can be 0 to many entries per region per day. I need to create a query where there is one row per day (...
Christopher Lang's user avatar
0 votes
1 answer
1k views

Left Join with only one of multiple matches in the right table

I have a database, where every entry may have multiple names, therefore I made a name database. While all names will be displayed otherwhere I need a list of all main entries with one representative ...
Benito's user avatar
  • 1
0 votes
1 answer
43 views

Different index selection and join strategy between QA and Prod environments in PostgreSQL

I have encountered a scenario where the same query on a PostgreSQL database is exhibiting different index selection and join strategies between the QA and Prod environments. I'm trying to understand ...
sujeet's user avatar
  • 235
0 votes
1 answer
42 views

Compositing ordered SQL tables without Joins

I'm currently exploring some weird ways of doing things and was wondering if anyone has any experience with the following methodology. Background I have a handful of NF3 tables, and NF1 source data. ...
Vocoder's user avatar
  • 117

1
2 3 4 5
40