Questions tagged [join]

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

Filter by
Sorted by
Tagged with
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
-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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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. ...
user avatar
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 | |...
user avatar
  • 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 ...
user avatar
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%' ...
user avatar
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 | ----------...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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=...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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, ...
user avatar
  • 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 | +--------------...
user avatar
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 ...
user avatar
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 ...
user avatar
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 --------------------------...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
-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....
user avatar
  • 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 ...
user avatar
  • 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 (...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 -------------------------------------- | ...
user avatar
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 ...
user avatar
  • 1
0 votes
1 answer
51 views

Understanding JOIN Syntax

Given: postgres=# \d foo Table "public.foo" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | ...
user avatar
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, ...
user avatar
  • 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) ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
-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 -----...
user avatar

1
3 4
5
6 7
38