Skip to main content

Questions tagged [join]

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

Filter by
Sorted by
Tagged with
0 votes
2 answers
854 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 ...
1 vote
4 answers
2k views

Get results from "combined, either inner join"

Inner join gives an "intersection" of two tables. And hence multiple inner joins give the intersection of all tables. What I--however--wish is to get an inner join that gets an intersection ...
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 ...
0 votes
1 answer
136 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 ...
0 votes
0 answers
130 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 ...
2 votes
2 answers
872 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 ...
0 votes
1 answer
139 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 ...
0 votes
1 answer
267 views

MySQL - Group By - How does it determine which row to return from joined table

I am getting desired, but inexplicable behavior on a particular query, and wanted to get more information before pushing up. I have 3 tables: foo bar bar_image And paring back all distractors, ...
0 votes
2 answers
123 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 ...
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 ...
127 votes
5 answers
323k views

Best practice between using LEFT JOIN or NOT EXISTS

Is there a best practice between using a LEFT JOIN or a NOT EXISTS format? What is benefit to using one over the other? If none, which should be preferred? SELECT * FROM tableA A LEFT JOIN tableB B ...
1 vote
1 answer
58 views

Why does SemiJoin node receive so bad statistic even after runnning ANALYZE

PosgtreSQL 9.4 I have two tables: CREATE TABLE home_transactions( id serial NOT NULL, transaction_id integer ); CREATE TABLE transactions( id serial NOT NULL, user_id, amount numeric(13,4) NOT NULL )...
1 vote
1 answer
88 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 ...
2 votes
2 answers
401 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
118 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 * ...
1 vote
1 answer
4k views

Postgres pg_trgm JOIN multiple columns with large tables (~50 million rows)

I'm trying to perform a query on Postgres using the pg_trgm extension that attempts to fuzzy match across multiple columns in two tables. I've let the query run for an hour or more and it hasn't ...
19 votes
2 answers
9k views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
2 votes
1 answer
9k views

How to optimize a query with multiple tables and left joins?

I'm trying to run a query on MySQL database, but the query takes too much time, even though the database is not too large, around 100,000 of rows in each table.I have not defined or created any ...
0 votes
1 answer
254 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 ...
1 vote
2 answers
257 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 (...
0 votes
1 answer
50 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
44 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 ...
0 votes
1 answer
43 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. ...
0 votes
0 answers
962 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 ...
9 votes
2 answers
61k views

Sql server table insert performance optimization

Setting In a datawarehouse, I am joining a fact table to 20 dimensions. The fact table has 32 million rows and 30 columns. This is a temporary staging table so I don't have to deal with other users ...
0 votes
2 answers
32 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 ...
0 votes
1 answer
39 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 ...
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 vote
2 answers
106 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 ...
-1 votes
4 answers
358 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. ...
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.
1 vote
1 answer
701 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 ...
1 vote
1 answer
29 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
97 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 ...
0 votes
1 answer
50 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 ...
3 votes
2 answers
4k views

Get fast count after join on "containment" expression

I have three tables in a PostgreSQL database that I'm querying via a view and some joins. CREATE TABLE network_info ( network CIDR NOT NULL, some_info TEXT NULL, ...
2 votes
2 answers
162 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 ...
0 votes
1 answer
254 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
77 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
197 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....
0 votes
2 answers
132 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(...
0 votes
2 answers
68 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 (...
1 vote
1 answer
99 views

Items from last month AND if not possible, items from this month

So the title is not really clear, but I could not think how to put it into shorter words better. My problem is: I have a database, which I use for web-scraping, with different articles and their ...
0 votes
2 answers
420 views

Slow query with multiple group by

I have a query to get all the comments from threads where a given user has commented and not yet seen the comment. So if user 1 commented to a topic, then user 2 commented after his comment, then user ...
1 vote
2 answers
1k views

LEFT JOIN LATERAL conditions and JSONB

I have a JSONB column (jb_column) that may have a key (let's call it schrodinger) or not. When the schrodinger key is present, it's value can either be null::JSONB or a regular JSONB object. First, a ...
1 vote
1 answer
118 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
33 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 ...
26 votes
2 answers
46k views

Postgres JOIN conditions vs WHERE conditions

Postgres newbie here. I'm wondering if this query is optimized or not? I tried to JOIN ON only the values that are 100% necessary and leaving all the dynamic conditions in the WHERE clause. See ...
49 votes
8 answers
98k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
1 vote
1 answer
78 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 ...

1 2
3
4 5
41