Questions tagged [join]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
572 views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly slow ...
1
vote
1answer
48 views

Select the first row returned from the join query based on a condition

I have 2 tables PRODUCT and MANUFACTURER Table1: PRODUCT, Columns: PRODUCT_ID,PRODUCT_NAME Table2: MANUFACTURER, Columns: PRODUCT_ID,MANF_ID,ITEM_NO,DEFAULT_MANF I have to select the default ...
0
votes
1answer
269 views

How to optimize UPDATE with a nested SELECT subquery?

I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do: In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0
votes
2answers
35 views

How to update data with a trigger function and a join

I have two relations : CREATE TABLE relation_a (id_a int PRIMARY KEY, field_1 int); CREATE TABLE relation_b (id_b int PRIMARY KEY,fk_a int REFERENCES relation_a(id_a), field_1 int); INSERT INTO ...
0
votes
1answer
26 views

Querying an enormous table (~150 million rows) with multiple joins - SQL Server

First off, I don't have SHOWPLAN permission on this server or permission to add indexes, etc. I am not a database administrator by trade; I'm just trying to query a database. There is already indexes ...
1
vote
1answer
27 views

optimizing Postgresql query with left join and indexes

I am trying to run the following query but it is taking forever to execute: Select Distinct ON (S1.sql_id) S1.msgdata as replymg, S1.time as replytme, S2.receiver, S2.time,...
8
votes
2answers
43k 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 ...
1
vote
1answer
546 views

UNION or SELF JOIN?

I have a table to hold parent and child posts together. +------+----------------+--------------+-------------+------------+ | p_id | parent_post_id | child_status | post_status | post_title | ...
0
votes
0answers
12 views

Is duplicating “short cut” foreign keys a good idea to avoid multiple joins?

I have 3 tables A, B and C. C belongs to B, and B belongs to A. 1 entity of A can have 0 or various entities of B, 1 entity of B can have 0 or various entities of C. 1 entity of C has 1 entity of B, ...
0
votes
3answers
41 views

update one column based on query containing another (joined) table

Consider the following select query: SELECT s.id, s.event, s.column, e.oldcolumn FROM section as s INNER JOIN event as e ON s.event = e.id WHERE s.id = 1 Now I wish to update the section table so ...
0
votes
1answer
20 views

Postgresql 4 table joins

I have one doubt in posgresql joins and filters. Please find below sql code snippets. 1) select count(*) FROM lfa1 a LEFT JOIN lfb1 b ON a.lifnr = b.lifnr and a.hvr_is_deleted = 0 and b....
1
vote
1answer
55 views

How to filter multiple many to many relationship based on multiple tags?

I've a scheme of two main tables: problem and tag and a relation (which is a many to many connector) table: problem_tags which excerpt of them is like: Problem table: ----+---------------------------...
2
votes
2answers
1k views

Performance of a big INSERT…SELECT with multiple JOIN

I'm having trouble with a big INSERT...SELECT in my DB. For context, the targeted table already have around a million entries and my SELECT query return approximately 2 millions. On a production ...
1
vote
0answers
22 views

How to return most recent element with distinct subquery

I have such query but it returns duplicated rows for same speaker SELECT "speakers"."id", "speakers"."name", "speakers"."surname", "speakers"."photo_url" as "photoURL", "talks"."...
0
votes
1answer
24 views

Postgres foreign data wrappers weird join behavior

So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins. Keep in mind the following snippets are ...
1
vote
3answers
39 views

Pre-filtering on right table in a LEFT JOIN

I have the following schema/query where I basically need to filter parent table by some criteria and then collect aggregated data from its child table(s): create table parent (id integer primary key, ...
3
votes
1answer
9k views

How does unnest() treat NULL values and empty arrays?

I am trying to achieve something like LEFT JOIN with array unnest() function. I want the query to return a row with a null value if the array is empty. Therefore, by using a CASE construct, I wanted ...
0
votes
1answer
115 views

same table on different commands (inner join)

Testing some commands of MySQL, I've come across these two comands resulting on the same table. Command 1: select sales.relatory_code, sales.date, destiny.exportation_name from sales, destiny where ...
6
votes
3answers
26k views

How to SELECT multiple COUNT(*) columns with each GROUP BY having a different WHERE clause?

I have the following schema: CREATE TABLE Person ( PersonId int PRIMARY KEY ) CREATE TABLE Action ( ActionId int PRIMARY KEY, PersonId int NOT NULL FOREIGN KEY REFERENCES Person(PersonId), ...
0
votes
1answer
31 views

Create result set with all records from LEFT table, and from the RIGHT table only bring records that do no exist in left already

I'm having a bit of trouble wrapping my head around a way to make this JOIN work. Postgres 11.5 I have two tables: Table1: customer_id date (various other fields) Table2: customer_id date (...
0
votes
2answers
4k views

need help joining two tables with a having count clause

I am a network guy so I apologize for this.... I have two tables I need to join and pull names out of. pt_basic has the patient names and codes I need, ptc_diagnosis has their diagnosis. I need to ...
0
votes
0answers
31 views

How to move columns into row in oracle

I want to query customer and cars table and show the result as above
0
votes
1answer
331 views

ERROR: subquery must return only one column

I have two tables one is Category and other one is Product. Table Description are Category name description parent_id (self referencing key) (can be upto 3 levels) Product name description type ...
13
votes
2answers
56k views

SQL join query to show rows with non-existent rows in one table

I am trying to get some reporting done for employee time records. We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of ...
14
votes
4answers
2k views

Is a join optimized to a where clause at runtime?

When I write a query like this... select * from table1 t1 join table2 t2 on t1.id = t2.id Does the SQL optimizer, not sure if that is the correct term, translate that to... select * from table1 t1, ...
0
votes
2answers
2k views

RIGHT JOIN did not work as expected

I have a question about joining two tables. Schema CREATE TABLE [dbo].[DCString] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [DCDistributionBoxID] [bigint] NOT NULL, [CurrentMPP] [decimal](...
0
votes
1answer
69 views

Looking for a snippet illustrating ambiguous result of a join in a where clause

A while ago (around 2004) I worked as a back-end developer on a team where everyone used the where clause joins. As a proponent of using inner/outer joins instead, I showed them an example of a where ...
6
votes
5answers
1k views

What is the point of outer in a join?

I have made a script which harvests mac addresses from switch ports and puts them in a database. I insert it into an import table to be copied over later to the real table. All fine. Now I am writing ...
0
votes
1answer
40 views

JOIN/Union Two tables, removing NULL where possible (similar to pandas concatenate)

I am wondering how to perform an operation that I guess is most akin to a concatenate if you're familiar with pandas. Basically, what I want to do seems like a UNION where records are "collapsed" in ...
1
vote
3answers
247 views

SQL Join and Count

I have 3 tables in SQLServer that I want to join so I can count the number of times each SUBJ_NAME occurs for the ITEM_IDs in the LOAN table. CREATE TABLE SUBJT ( SUBJ_ID CHAR(4) PRIMARY KEY, ...
0
votes
1answer
15 views

How to get latest timestamped result from time series in a join table [duplicate]

I have 3 tables node ---- id ip metric --- id key (str) node_id (FK on node) his_metric ---- id metric_id (FK on metric) value (float) datetime (datetime) Question How to write a query that will ...
0
votes
0answers
12 views

Debugging performance differences (and issues) between a query with a double-subquery, a single-subquery and an all inner-join statements

I have a complex business logic that requires me to perform a 2-levels nested query. The queries are generated by Django's ORM. At the bottom of the question I'll provide the queries as-is as well as ...
0
votes
3answers
39 views

How do you join three or more records from the same table?

I have table with a lot of data in it, and we would like a way to essentially flag certain documents to indicate that they are related to (x) other documents: TABLE A ----------- id ...
0
votes
1answer
131 views

How to query Last inserted row for each group in oracle 10

I have two tables about users where I want to join two tables and show the latest remarks of each user bellow is table I want to query the latest remarks for each user (1 remark per user) bellow ...
1
vote
0answers
11 views

Calculating forecast data in parallel

I'd like to ask about how to calculate forecast data in parallel in a relational DB like PostgreSQL, which seems like a very typical problem when doing forecasts. Let's say we're trying to get the ...
0
votes
1answer
16 views

Need to Bring Back most Recent Departure Date from Sales table for Customers in Recipient table

Looking at 3 Tables - Recipient, HouseholdScores, Sales. I need to bring back the most Recent tsDepart (departure date) from sales for the iCustomerId in the Recipient Table. There is no Depart date ...
1
vote
1answer
35 views

How do I optimize my transaction level running balances cartesian join?

This is a continuation of an question found here: Generate multiple running totals with GROUP BY day This is a continuation of a previous question, where table definition and sample data can be ...
1
vote
1answer
17 views

Return previous running total when value is null in a time series

This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there). All of this is being done on a PostgreSQL ...
1
vote
1answer
36 views

Generate multiple running totals with GROUP BY day

I have a set of transactions of stock purchases by users and I want to keep track of a running balance of each stock as the year progresses. I am using a windowing function to track the running ...
0
votes
0answers
31 views

Why does UNION fail here?

I have a problem that I can't seem to figure out how to fix. What I want to do is simple, select data and append a row of sums as the last row. But I keep getting the error An error occurred in ...
12
votes
3answers
30k views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
-1
votes
1answer
25 views

SQL joining three tables and displaying column with values zero count

Im trying to display all the city names and their counts of events where the event price = 0 create table event (eventID decimal(12) not null primary key, AddressID decimal(12) not null, eventName ...
1
vote
1answer
25 views

Show all rows even when count is null

I want to see all the rows from one table and then do a count on the second table to see how many match, but want to see it all even if there are 0 matches in the second table. My query: SELECT a....
0
votes
0answers
34 views

Optimise the selection of two distinct column pairs from the same table

I need help with rewriting or editing the query/logic to improve it's performance. SQL Server 2014 I have the table structure specified below. I want to select two column pairs: (SourceID + ...
0
votes
1answer
13 views

Ordering a parent table by SUM() of colums of two of its child tables

I know that title might sound confusing, but i will try to explain my problem as much as i can. I have four tables: companies{ id, name, address } stores{ id, name, address, company_id } invoices{ ...
2
votes
1answer
1k views

Multiple to_json(array_agg), separate joins

I'm building a messaging system, and trying to get a list of messages and their replies; and also join the participants of the message threads. So I'm shooting for messages = [{id, subject, body, ...
0
votes
1answer
41 views

Get data from different tables indexed by same ID

Database: postgresql 12. I have a table people: person_id | name ----------+----- 1 |John 2 |Paul 3 |Jim ... Each person can have zero or more car insurance contracts, so I ...
0
votes
0answers
17 views

Join and pivot 2 tables related by an intermediate one

This is probable a very silly question for a DBA but I'm not one and my SQL knowledge its pretty limited. I'm actually making a simple DB to store my electronic component models (footprints and ...
0
votes
0answers
16 views

Matching columns within the same table in SQLite

I have a table of Employees which contain, among other things, the employeeId, the lastName and the ReportsTo column, that refers to the manager ID to which the employee has to report. That manager is ...
0
votes
1answer
35 views

I dont know where to begin: mysql select with inner selects on iterations too slow with bigger tables ( 4 tables on 2 databases)

Kontext is easy: A) A [discussion-thread.db1] has many [Posts.db1] B) each Post has an [author.db2] and 1 to 10 [attachments.db1] Additional Information: discussion, posting & attachment's-...

1 2
3
4 5
34