Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,971
questions
0
votes
2
answers
45
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 ...
- 11
0
votes
1
answer
50
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 ...
- 123
0
votes
0
answers
16
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 ...
- 101
2
votes
2
answers
215
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 ...
- 123
0
votes
1
answer
18
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 ...
- 1
0
votes
1
answer
38
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
...
- 33
0
votes
2
answers
40
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 ...
- 197
3
votes
5
answers
1k
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 ...
- 1,041
2
votes
2
answers
119
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` ...
-3
votes
1
answer
75
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 * ...
- 97
1
vote
1
answer
60
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
...
- 11
1
vote
2
answers
48
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 (...
- 113
0
votes
0
answers
34
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 ...
- 221
0
votes
1
answer
27
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 (...
0
votes
1
answer
211
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 ...
- 1
0
votes
1
answer
41
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 ...
- 213
0
votes
1
answer
35
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. ...
- 117
0
votes
2
answers
20
views
SQL multilevel join how to add a where condition
I have the following SQL query
SELECT
r.id as id,
reports.Timestamp as reportTimestamp,
reports.Latitude as reportLat,
reports.Longitude as reportLng,
FROM Requests r
LEFT ...
- 141
0
votes
0
answers
159
views
MySQL indexes on temp tables
I am trying to perform joins between two temporary tables, creating an index on the join key for each temp table. However, when I EXPLAIN the join, the row estimates always seem to come out vastly ...
- 1
0
votes
1
answer
34
views
Join two tables with similar columns
I hva two tables as is:
Table univers:
client
commercial
Axel
Tony
Mike
Jhon
Chris
Jhon
Table transactions
frclient
toclient
commission
Axel
NA
10
Mike
NA
8
NA
Mike
15
Axel
NA
4
NA
Chris
5
...
- 33
0
votes
0
answers
23
views
How to Join two tables with SUM in SQL server [duplicate]
i have 2 tables
1.registros
2.servicios
what I wanna have is this: id,nombre,apellido,direccion,telefono servicios_pedidos of table 1 + sum of table 2 servicion.precios with table 1 registros.[...
- 1
1
vote
2
answers
44
views
How to leave only the rows with an timestamp difference with an interval greater than a parameter
I have a Postgres table with timestamps as entries.
From this table, I would like to calculate a new one where there are no consecutive entries with a timestamp difference shorter than 400 ...
- 11
0
votes
1
answer
13
views
Issue in Retrieving data from Table when it is having more than 1 records for 1 ID
I have a table ABC and it has 5 columns and now I need to find such records in the table for same ID value where the value in columns C is 0 in all the cases.
Solution please.
0
votes
4
answers
102
views
LEFT JOIN 3 tables NULL rows not as intended
Scenario - a sailing event with entrant categories.
I am a volunteer and I am struggling to get a result table for each entrant listing their selection (or NULL if none chosen) for each category. ...
- 1
0
votes
1
answer
94
views
Joining Columns with different character set
I am joining two tables on varchar columns having CHARACTER SET utf8 COLLATE utf8_bin and one with CHARACTER SET latin1. Indexing is not working when i am joining these two tables on this column, I am ...
0
votes
1
answer
23
views
update row with age calculation with DOB in related table
SQLite database with a table "events" with a columns events.eventDate, events.ageAtEvent and events.foreignKey_participants, and a table "participants" with columns participants....
0
votes
1
answer
30
views
Select tables data with sum of other tables
I am very new on MySQL DBA and i searched but i am not clearly understanding inner join with sum.
user table:
id, username, locationid
history table:
id, username, locationid, spent
Example user ...
0
votes
1
answer
70
views
SQL Server 2014 Cardinality Estimator estimate final number of rows after OUTER JOIN is less than number of rows from initial table
I have a [SQL query] for SQL Server 2019.
It works fine with option
(USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
and very bad without this hint.
I found out, that SQL Server 2014 Cardinality ...
2
votes
2
answers
70
views
Join order and nullability
I am asking this question in the context of using a Postgres database, although I would imagine it would apply to most SQL databases.
When working with the results of a database query inside a ...
- 243
0
votes
1
answer
53
views
ORA-12015 materialized view complex query error with simple join two table
I have 2 simple table which has columns like :
table1 :
"OBJECTID" NUMBER NOT NULL ENABLE
"OLCEK_ID" NUMBER(*,0)
table2 :
"OBJECTID" NUMBER NOT NULL ENABLE
i created ...
1
vote
1
answer
76
views
Worst performance multiple left join big tables after restore DB
Problem
Database: PostgreSQL 13.8 (Debian 13.8-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
After migrate from DigitalOcean to strong Dedicated Servers ...
1
vote
1
answer
64
views
What's the fastest way to query multiple tables at once where each table represents one year of data?
I'm developing an web app based around a database with multiple decades of data with multiple schema changes over the years. Each year of data is released as a zip file containing CSVs named table1....
- 11
0
votes
2
answers
83
views
How to fill a join table with one SQL statement in MySQL?
I have these tables
CREATE TABLE users
(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(15) NOT NULL UNIQUE,
password VARCHAR(200) NOT NULL,
name VARCHAR(...
- 135
0
votes
2
answers
64
views
Join and use most recent data if NULL
Table WSHOSHO
SELECT * FROM WSHOSHO;
Table RRP
SELECT * FROM RRP;
Join to illustrate issue
The results of this join can be used to illustrate the issue:
SELECT * FROM WSHOSHO RIGHT JOIN RRP ON (...
- 165
1
vote
1
answer
19
views
Listing Entries from Table1 which do not appear in Table 2
Currently I am working with 2 tables... one table consists of jobs carried out and also the invoice number... there could be multiple jobs for a customer on the one invoice... the table might look ...
- 167
1
vote
1
answer
62
views
How I can retrieve the latest records without using lateral join?
I have the following database:
create table "client" (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
lastname VARCHAR NOT NULL
);
create table store (
id SERIAL PRIMARY KEY,
...
1
vote
1
answer
69
views
Mismatch between the total number of rows in a sql join
When I join entries from the points table (n = 34, 436) to the mid_pts table (n=1.98 million) the total number of rows of the table_joined is n = 26,016. However, when I add the unjoined_pts table of ...
- 123
1
vote
1
answer
398
views
Optimal join for joining facts with scd-type-2 dimension for aggregation/reporting
I have a fact table and an scd-type-2 dimension table. I want to produce sales report by region and year.
I have working solution with a query that joins them for reporting purposes. When I run the ...
- 149
0
votes
1
answer
33
views
Postgres join as inner json
Just trying to do a simple inner join as json.
model
posts (author is fk to profiles.id)
id
author
title
...
profiles
id
displayName
...
Trying to do a simple join so that:
select * ...
- 143
0
votes
1
answer
85
views
MySQL query takes too long to execute
I'm working on an IOT based application for which I'm storing time series data in MySQL. I have two tables in different databases as follows:
describe fo_data.device_data;
+-----------+----------------...
- 101
2
votes
2
answers
108
views
Straightforward inner join is slow despite using indices and ref/eq_ref types
I have a couple moderately-sized MySQL 8.0.30 InnoDB tables:
models - 1,000,000 rows
activations - 10,000,000 rows
A fairly simple INNER JOIN takes about 500ms, which seems like a lot (is it?). This ...
- 131
0
votes
0
answers
27
views
how to make joins on different tables based on the data retrieved, with TimeScale/Postgres
Here is a simplified version of the problem I'm trying to solve:
Table A with id and some data:
id, data
A01, xxxx
A02, xxxx
...
Table B with id and some other kind of data
id, data
B01, xxxx
B02, ...
- 273
1
vote
1
answer
153
views
Find duplicate values by joining tables SQL server
Finding the duplicate values in the 'Item_Sales_Detail' table as NULL rows in the 'Sales' and 'Item' tables by joining three tables.
'Sales' table (ID is primary key)
ID
Invoice
Date
TotalAmount
10
...
0
votes
0
answers
88
views
Redshift / Postgresql query multiple tables faster than Natural Full Join
I have been given a requirement to query 8(+) different Redshift tables and return some of the fields where email = provided email address.
Current implementation:
(select product_name, email, ...
- 101
0
votes
0
answers
27
views
Avoid Duplicates Record
SELECT DISTINCT log.employee,
log.attendance_date ,
case when ch.log_type = 'IN' then TIME_FORMAT(ch.time,"%H:%i:%s") end as attend_time ,
case WHEN ch.log_type = 'IN' THEN CONCAT(...
- 1
0
votes
0
answers
20
views
In rest api how to get parent_id have child_id in same database using node.js and used at regressive function
{
"error": false,
"message": "Data fetched successfully",
"exam_format": [
{
"id": "b2a9f30f-04bf-4735-b293-...
-1
votes
1
answer
632
views
Retrieve all data from an inner join with duplicate column names in MySQL
I have a database with tables for "users" and "profiles". Every profile links back to a user. I would like to do an inner join where I am able to retrieve a set of profiles that ...
- 123
0
votes
1
answer
40
views
MYSQL: Return a predefined value if result of join is null
Before all, sorry my very bad english level.
Here's my problem.
If i have two tables:
'Products'
|id | product |
|---|---------|
|1 | "Fork" |
|2 | "Spoon" |
|3 | "Knife&...
- 23
0
votes
0
answers
30
views
Joining Two Tables via JSONB column (date)
Given the following data:
create table datas (id int, data jsonb);
insert into categories (id, budget)
values
(1, '{"2022-07-01": {"budget_amount": value1, "some": &...
- 43
-1
votes
1
answer
53
views
Add Some custom Text at the place where value is null in the join?
I want to join two table in such away that If there is no value against first table entry in the second table then it should fetch that row but insert some custom text in that row.
Is there any ...
- 133