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
10 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.
Nitin Punj's user avatar
0 votes
4 answers
49 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. ...
Chris H's user avatar
0 votes
0 answers
20 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 ...
Manish Kumar's user avatar
0 votes
1 answer
20 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....
Stam Kapetanakis's user avatar
0 votes
1 answer
18 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 ...
Imran Ahmed's user avatar
0 votes
1 answer
50 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 ...
Андрей Ерёмин's user avatar
2 votes
2 answers
63 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 ...
Daniel Rearden's user avatar
0 votes
1 answer
24 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 ...
CartWheel's user avatar
1 vote
1 answer
70 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 ...
M. Garbarczyk's user avatar
1 vote
1 answer
37 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....
Tyler's user avatar
  • 11
0 votes
2 answers
50 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(...
Sergey's user avatar
  • 135
0 votes
2 answers
61 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 (...
dharmatech's user avatar
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 ...
PaulMcF87's user avatar
  • 159
1 vote
1 answer
49 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, ...
Dimitrios Desyllas's user avatar
1 vote
1 answer
62 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 ...
MJM's user avatar
  • 123
1 vote
1 answer
172 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 ...
Kashyap's user avatar
  • 149
0 votes
1 answer
26 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 * ...
Jonathan's user avatar
  • 133
0 votes
1 answer
79 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; +-----------+----------------...
mrid's user avatar
  • 101
2 votes
2 answers
65 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 ...
mgiuffrida's user avatar
0 votes
0 answers
25 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, ...
Thomas's user avatar
  • 261
1 vote
1 answer
74 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 ...
Mohammad Bastan's user avatar
0 votes
0 answers
30 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, ...
AylaWinters's user avatar
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(...
hisham_y's user avatar
0 votes
0 answers
14 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-...
Backiyanathan M's user avatar
-1 votes
1 answer
260 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 ...
Eric Johnson's user avatar
0 votes
1 answer
23 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&...
Matt Ross's user avatar
0 votes
0 answers
24 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": &...
RMcLellan's user avatar
-1 votes
1 answer
39 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 ...
Manii's user avatar
  • 123
0 votes
1 answer
284 views

Bad inner join performance on one-to-many relationship

I have a question regarding the postgres planner that I can't wrap my head around. I am trying to get the planner to look up a project ID by its unique key before running the other side of the query ...
mreaglejr's user avatar
2 votes
1 answer
25 views

Every value in Column 1 Needs every other value in Column 1 as a new row

I am stuck on some code. As shown in the image, I need each state to be given a new row for every other road project in the country. Ohio, for example, is given 2 new rows in the new table, 1 for each ...
Illy's user avatar
  • 23
1 vote
1 answer
64 views

Combining Multiple queries from the same table inrto 1 query

I am looking to find a way to achieve the below... I have tried using Joins and SubQueries but no joy as yet... I have a table that keeps track of wins/loses/draws as so Date WLD 01/01/23 Win 05/01/...
Paulmcf1987's user avatar
0 votes
1 answer
22 views

PostgreSQL INNER JOIN With Most Current Records

I am trying to create this VIEW: CREATE VIEW highlight_on_rss_feed AS SELECT highlight_on_article.title AS title, highlight_on_meta_description.meta_description AS description, concat('...
Ron Piggott's user avatar
0 votes
1 answer
21 views

merging 2 Queries into one

I have the following queries: Query 1: SELECT agencies.code, SUM(IF(commissions.action = 'collection', commissions.commission, 0)) as collections, SUM(IF(commissions.action = 'delivery',...
user268130's user avatar
1 vote
1 answer
22 views

MySQL Query - Sum data according to Category Parent name - each their own column

Here is the data I have: table agencies: id code 18 agency_01 6 agency_02 table compensations id agency_id compensation_category_id amount registered_at 3 6 4 45000.00 2023-01-01 4 6 5 25000.00 ...
user268130's user avatar
3 votes
3 answers
541 views

Join twice on same table with different conditions and no PK / FK relations

I have the following tables: agencies: | code | commissions: | registered_at | action | origin | destination | commission | I need to SUM(commissions.commission) for the following conditions ...
user268130's user avatar
0 votes
0 answers
23 views

Select columns from joined tables, group or distinct by a single column, and order by another column

Here's essentially what I'm trying to do: SELECT deals.*, retailers.title AS retailer_title, ST_Distance(retailers.lonlat, 'SRID=4326;POINT(-118.4104684 34.1030032)' :: geography, 15 * ...
JohnDoe1999's user avatar
0 votes
0 answers
21 views

How to retrieve 2 column from 2 tables, column 1 and 2 are count from two tables

How to retrieve 2 column from 2 tables, column 1 and 2 is (count) from two different tables and where condition from another table (table 3 ) my query is : SELECT ( SELECT COUNT(*) FROM `...
hisham_y's user avatar
0 votes
1 answer
103 views

How would I use "LEFT JOIN" and "IF NOT EXISTS" in this case?

There're 2 main tables: drivers and driver_riders that are connected by a foreign key. Every day some of the drivers make a single or multiple rides. There's also a table driver_rewards driver_rewards ...
Camila326's user avatar
1 vote
1 answer
119 views

Why is it called "semi-join"?

If my understanding is correct, a semi-join is a way of selecting records from one dataset that have related (via a common attribute) records in another set. So where does the "semi" come ...
Johntron's user avatar
  • 1,723
1 vote
1 answer
67 views

Poor performance when at least one of the left joins should match a record

I've got bookings (bookings) in my database. A booking can have 0 to n flight services (flight_services) and 0 to n hotel services (hotel_services). A user on my website can filter the bookings by ...
quick-brown-fox's user avatar
1 vote
0 answers
134 views

Postgres sorted join with millions of rows, low cardinality, slow query - time to partition tables?

I have an API that is used to query for data about programs and other forms of content. We have a few tables that I need to join on to fullfil an ad-hoc query, and I am running into trouble speeding ...
flanders9er's user avatar
0 votes
0 answers
34 views

Optimization of google query with multiple LEFT JOIN and OR operators!

I have a SQL query that runs twice daily and ends up spilling disk space, the query selects data from left joining three tables and use multiple OR operators to fetch results and then insert all ...
Greg 's user avatar
0 votes
2 answers
28 views

I have a database with a player, tournament, and registration table. I need to query all players that played in 2 types (attributes) of tournaments

I currently have two tournaments that have matches. Wimbledon and Atlanta open which I have one as a grand slam and the other as a masters 1000, and I want to get the players who played in both This ...
zaria palmer's user avatar
1 vote
0 answers
29 views

Multipile queries vs a long query with joins in my situation

I am currently working on a project where, each user can possess multipile portfolios. Each portfolio contains products, each product contains assets, which can be associated to different kinds that ...
orangetiger's user avatar
1 vote
0 answers
41 views

Increasing the 'stoptime' of generated routes

I'm learning some basic SQL skills and I was toying around with some data to see what I can do, however, I'm a bit stuck on how to proceed with my current problem. I have a table that looks like this: ...
Mathematician 42's user avatar
1 vote
1 answer
47 views

Is there any advantage using non-equi join instead of where clause in Oracle SQL?

I've been studying Oracle SQL non-equi join and while there are so many different use cases, I noticed that joining tables using a range of values with non-equi join is basically the same as using ...
user8491363's user avatar
2 votes
1 answer
41 views

How to combine three tables for a queue in each Store in each town?

Sorry if my question is no appropriate for DA. I have a State-city table with unique id for every town/city name. (id: town --> city --> State) I also have a list of Store names associated to ...
C.F.G's user avatar
  • 131
0 votes
0 answers
25 views

Would INNER JOIN on equality of two keys delete rows effectively?

Ī̲ repair a MediaWiki site after a DoS attack which injected terabytes of garbage into the text table. Ī̲ found that DELETE with clauses works painfully slow and, even worse, locks the table; that is, ...
Incnis Mrsi's user avatar
1 vote
1 answer
51 views

How to efficiently query with a join and a where/order across two tables?

I have two PostgreSQL tables: contents id (primary key) captured_at (date) status (enum) plenty of other columns we don't care about library_contents library_id content_id With three indexes on ...
Basile Bruneau's user avatar
1 vote
0 answers
45 views

database design - sales, products, customers, persons and organizations

I am trying to model a system where you can have persons or organizations as customers. persons or organizations can also have other sub-types like employees, suppliers and other types in the future. ...
JaySmi's user avatar
  • 21

1
2 3 4 5
39