Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,889
questions
0
votes
1
answer
25
views
Multiple left joins with common fields are overwritten
Tables
transactions t
transaction_type
transaction_name
sale
one
sale
two
sales s
name
amount
one
10.0
two
5.0
purchases p
name
amount
Wanted
transaction_type
transaction_name
amount
sale
...
- 103
1
vote
1
answer
279
views
When to use a "middle table" instead of storing a JSON with IDs?
I'm working on a store for a mobile app, the database is mysql.
I have the store items in a table called items and that table has a column called price_point_id (one item has 1 price point) which ...
- 11
1
vote
1
answer
63
views
Getting the latest timestamp in Oracle
I am trying to create a report that has the latest member id in the first column and the old member id in the second column based on the timestamp in audits table below. Member ids are stored in ...
- 11
0
votes
1
answer
72
views
Tuning query - reduce temporary and/or filesort use with join
My query runs in 60-90 seconds, I would like to see how can I improve it.
One table contain partitions and have tens of million rows each partition.
The other table contain 5-8 million rows between ...
- 425
1
vote
2
answers
100
views
Time based sampling
Is there any way to do sampling on time series data in PostgreSQL (v11) based on a fixed time interval?
For example, we are storing 1 record per second for 24 hours in a given table. This means ...
- 113
0
votes
1
answer
293
views
How do I join a parent table to two child tables without duplication?
parent table
id
city_name
zip_code
1
new york city
10001
2
los angeles
90001
3
chicago
60007
Customer, child table
id
customer_name
city_id
address
1
alice
1
428 Hartford Lane
2
betty
1
249 ...
- 3
1
vote
1
answer
37
views
Latest value of each GROUP with INNER JOIN
I'm using sensors to get temperature and humidity every X minutes.
I've one table for sensors and one table for their results.
tb_sensors:
tb_results:
I want to get the latest measurement for every ...
- 23
-1
votes
1
answer
39
views
How to get distinct count on sub table joined via two different way
I have 3 below tables in my db
CREATE TABLE main_table
(
id SERIAL PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE middle_table
(
id SERIAL PRIMARY KEY,
name ...
- 149
0
votes
1
answer
41
views
Joining without a unique identifier
Payments
PaymentRef
Product
PaymentAmount
MerchantRef
P01
ABC
100
MR01
P02
ABC
200
MR02
P03
XYZ
200
MR03
Refunds
ReundRef
Product
PaymentAmount
MerchantRef
R01
ABC
-10
MR04
R02
ABC
-100
MR05
...
1
vote
2
answers
955
views
MySQL: Data pagination according data on another table
I have a database with the following structure
Items table: itemID, itemName
Ads table: AdsId, itemID
I would like to make pagination on items table, 10 items in each page, but first I must retrieve ...
- 113
0
votes
1
answer
370
views
Calculate Percentage with JOINS and original value
I need your help. I'm trying to calculate percentages by using results from JOINING divided by the original value. I do not know how to combine both values. I try to find trips with station id which ...
- 1
1
vote
1
answer
22
views
sqlite 3.4 updating table with data from different table - changed with 3.3 - newbie question
All of the other questions on this seem to be before 3.3 when sqlite added an update join feature? Newbie here, so I may even be getting the terminology wrong. I am trying to update the issbn field in ...
- 11
0
votes
1
answer
98
views
MySQL - GROUP BY with JOIN query
I am using MySQL 8.0.23
I have 4 tables: houses, intervals, discounts, and availability.
Houses:
create table houses
(
id int auto_increment
primary key,
name varchar(255) null,
...
- 45
0
votes
3
answers
25
views
Get the ID of a next greater/less Value
imagine you have follwing table given
id
value
1
5
2
3
3
7
4
1
Depends on your case you need the ID of the next smaller
or greater value.
So the select of ID=2 would give u either 4 or 1.
How ...
- 1
-1
votes
2
answers
44
views
Query all of a sudden stopped working - Simple left join but cannot see what is wrong
I had a query that was a simple left join between two tables with IS NULL included in the where clause because I needed all of the rows of the left table shown, even if it gave null values in the ...
- 15
1
vote
0
answers
269
views
SQL: ON vs. WHERE in SEMI JOIN
Considering the following two (equivalent) SQL statements.
Why do their execution plan differ (especially in performance) that much?
I'm using Postgres 11.9 in this example.
select p.name
from person ...
- 111
0
votes
2
answers
364
views
MySQL - Many to Many relationship, or one to many with additional columns
Our product contains some constraints that made us contemplate which table design will yield the best performance.
Our model:
Question table which contains fields like id, body, difficulty, and tags.
...
- 101
1
vote
2
answers
241
views
Get rows above and below (neighbouring rows) a certain row, based on two criteria SQL
Say I have a table like so:
+---+-------+------+---------------------+
|id | level |score | timestamp |
+---+-------+------+---------------------+
| 4 | 1 | 70 | 2021-01-14 21:50:38 |
|...
- 15
0
votes
2
answers
85
views
Count Rows that exist before and not after and after but not before
I have an Azure SQL Database, one Table that has 3 Relevant Columns: Brand, ID and Month/Year and a Temporary Table that simply lists the Month/Year with an ID.
The table shows a list of all IDs that ...
- 73
0
votes
1
answer
32
views
How to join 2 tables when second table will return more rows?
im trying to join 2 tables with condition - if it matches than im expecting to get all results from another table.
Current querry that doesn't work :
SELECT (CASE WHEN ZL.NumerCzesci LIKE '%ZŁOŻENIE%' ...
- 3
5
votes
2
answers
845
views
Postgres not returning data on array_agg query as below
The problem arise when there are no data for books in specific library. Consider a following working scenario.
Table library
--------------------------------
| id | name | owner |
----------...
- 209
2
votes
1
answer
312
views
PostgreSQL Query with pagination is slow when using Merge Join
I am experimenting with trying to optimize a query that's used to iterate over pages of rows with a joined table. After changing the query from using OFFSET to WHERE id > last_id_from_prev_page and ...
- 123
0
votes
1
answer
52
views
Get monthly sums from several tables with same (date,amount) columns
I have records stored in "date,amount" columns from several tables: table1, table2, table3, table4. Amongst tables, field names are same.
Table 1:
date
amount
2018-02-17
10.00
2018-06-25
...
- 131
1
vote
1
answer
525
views
CASE statement not returning expected results [closed]
First time using Postgres and building a query with a CASE statement. I am trying to end up with a collection of data whereby one of the columns in the data (status) qualifies the row based on ...
- 119
0
votes
1
answer
69
views
How MySql Join two tables with Checkboxes results stored as "string" data format?
Suppose I have 2 tables obtained from a web form submission, with some checkboxes results stored as "string" format as describerd here in "hobby" column https://dbfiddle.uk/?rdbms=...
0
votes
1
answer
74
views
Why i can't use table name in subquery?
I have a MS-ACCESS database. When i try to do this query:
SELECT *
FROM
(SELECT * FROM tblClients AS a1 LEFT JOIN
(SELECT TOP 1 lVersion, lRequestID AS lVerReqId, lClientId FROM ...
- 11
0
votes
1
answer
53
views
Filter on join condition and performance
I have a query of the following type:
SELECT *
FROM CUSTOMER C
JOIN USERTABLE U on C.AccountmanagerID = US.ID
The user table also has external users which never can be the accountmanager. This table ...
- 3
0
votes
1
answer
20
views
Join static file xml with ID field with Database table ID field [closed]
I am using php to display a table with different columns. The table is build from a static xml file with several hundreds of entries. Each entry has also an ID field.
I have to look up that ID field ...
- 109
4
votes
1
answer
152
views
Same Join Constraints on Multiple Columns
We have a situation that I have been able to recreate with the following simple example. I have the following two sample tables:
CREATE TABLE contact_info
(
id INT UNSIGNED AUTO_INCREMENT,
...
- 245
1
vote
3
answers
28
views
SELECT using JOIN where result counts per join are less than n?
I am trying to query tableA, joining or sub-querying on tableB, where the count of matching rows from tableB are less than 4.
Example:
tableA
+-------------------+
|columnA|columnB |
+--------------...
- 125
0
votes
1
answer
39
views
oracle sql joins returns multiple row than expected
I wanted to join table zz_value with zz_gg.
table zz_value looks like:
nach_id ins_id pos_id value data_id gg_id
2416 647415 176959 266355 166991 9863
2416 647421 176959 266355 166991 ...
1
vote
0
answers
85
views
Preventing three SQL Left Joins from creating duplicate rows
I have 4 tables [tA] is the main data point. A customer could have more than one ID Code, or the same ID Code which has been renewed, and ID Codes can be transferred between customers, but a customer ...
- 11
0
votes
2
answers
248
views
Limiting join to top 1 row for each row
I'm trying to join two tables and filter join results but I couldn't manage it and I'd appreciate any help.
I have these row sets:
Table A
id | userid | targetid | start_date
--------------------------...
- 101
0
votes
1
answer
431
views
Oracle outer join by (+) to Postgres conversion
I migrated from the Oracle database to Postgres, but some SQL scripts crashed. I investigated the errors and I found out that Postgres doesn't support outer joins using by (+) convention.
I tried to ...
- 113
2
votes
1
answer
69
views
Iterate over smaller table instead of bigger one
So, I have 2 tables:
events
(
event_id primary key
date,
-- other data
)
actions
(
action_id primary key,
event_id references events(event_id),
-- other data
)
(relation is 1 ...
- 121
1
vote
1
answer
60
views
Join tables and summarize rows based on condition
I have two tables.
One containing countries with an ID and the geometry
The other containing customers with a name, geometry and a boolean value showing if they are active or not.
Countries
iD ...
-3
votes
1
answer
52
views
Is there a different (better) way to do this? [closed]
I have one parent table(p) with primary key (id) and two child tables (c1, c2) with foreign keys (pid), i want to return all the records including no matches where id = 1.
SELECT p.id, c1.data1, c2....
- 1
0
votes
1
answer
30
views
Join to get most specific record for tenant without PIVOT
I have a Settings table and a Tenant table.
There is a hierarchy for that an Account can have 1 or many Companies and a Company can have 1 or many `Facilities'.
Account 1
---> Company 1
...
- 393
0
votes
1
answer
71
views
Multiple self joins where joining condition not always exists
I am trying to perform a multiple self join query where one of the conditions it is joining on does not always exist. I have a table that stores different sale events, where a single transaction (...
- 3
1
vote
1
answer
83
views
Difficult multi-directional join
I'm being asked to create what (at first) seemed to be a fairly simple/straight forward query. However, I am running into some problems caused by the link relationships between the tables being ...
- 153
0
votes
1
answer
43
views
only use extended join-clause if some variable is TRUE [closed]
I am working with Postgis (extension to postgres) where there are several different geometry-types, i.e. 'polygons', 'points' 'linestrings', and multi-variants of these.
I am trying to create a ...
0
votes
1
answer
53
views
How join 2 table with calculation in the relation ID
I'm using SQL Server 2012 for my RDBMS I have 3 main table Tb_Template,Tb_Value, and Tb_Mapping.
I use Tb_Mapping to join Tb_Template and Tb_Value. But the relation to Tb_Value have calculation ...
- 3
1
vote
1
answer
1k
views
Combine Multiple Rows Results From Left Join in a unique cell
¡Hi guys!, I don't have any idea how to create a new column that contains all information of results from a Left Join if exists the value
Example
Table 1
--------------------------------------
| ...
- 21
0
votes
1
answer
129
views
Joining two tables based on two Aggregate columns
I have a following problem as in the example. I have to join two tables based on the aggregated columns. These are the only way to combine these two tables. Is it possible to do this? I have tried ...
- 1
0
votes
1
answer
51
views
Understanding JOIN Syntax
Given:
postgres=# \d foo
Table "public.foo"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | ...
- 475
0
votes
1
answer
179
views
Number of logical reads in SQL server JOIN
Please refer to the SELECT query shown below, which includes a JOIN between two tables that I have renamed as Table1 and Table2, where the latter is a temporary one:
Table1 has over 15M records, ...
- 163
0
votes
1
answer
63
views
How to improve the estimated cardinality of inner join lateral
I'm currently running a query that has the following structure:
select
v1.*,
v2.*,
...
from t1
inner join lateral f1(t1.id1, t1.id2) as v1 on true
inner join lateral f2(t1.id1, t1.id2) ...
- 103
0
votes
2
answers
47
views
Independent aggregation of related entities, while also sorting by related (in a single statement)
I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named ...
- 101
0
votes
1
answer
29
views
Match two distinct values from a single column in a joined table
I have three tables:
class:
class_id
class_name
student:
student_id
student_name
class_schedule:
class_id
student_id
I want to select all the classes where studentA and studentB are in the same ...
-1
votes
1
answer
104
views
CREATE VIEW WITH LEFT JOIN - FILTER RIGHT DEPENDING ON LEFT
I am using Oracle 12.1.
I have the following tables, simplified for the question :
(Note that the XXX for entity ID does represent the same ID being repeated)
Events
date_event | entity_id | used
-----...
- 57