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
21 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 -------------------------------------- | ...
0
votes
1answer
29 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 ...
0
votes
0answers
14 views

How to create a trigger that updates a table that may be joined to the table being queried

I am having trouble crafting a trigger. I have a database that has contacts in it, and the contacts table joins to a second table, contact_addresses. They are big tables, but here is the basic premise:...
0
votes
1answer
33 views

Understanding JOIN Syntax

Given: postgres=# \d foo Table "public.foo" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | ...
0
votes
1answer
72 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, ...
0
votes
1answer
34 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) ...
0
votes
1answer
20 views

Independent aggregation of related entities in a single statement, while sorting by related

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 ...
0
votes
1answer
27 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 ...
0
votes
1answer
81 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 -----...
0
votes
1answer
68 views

SQL CTE Expression with Cross Join - Can it be simplified?

I am looking to simplify the below SQL Query. Currently it's taking 44 mins to run. The main issue seems to be the WHERE [Versions] = (SELECT max([Versions]) from BPC_CTE) statement. What I'd like to ...
0
votes
1answer
22 views

MySQL Insert/Update across 3 tables with 1m+ rows

To start with, I know nothing of database design, so I apologise if this seems obvious to others. I have been researching up to 3NF over the last few weeks, and I think I have a layout that works. I ...
0
votes
2answers
31 views

SQL join on col1 if present else use col2

There are two tables T1(col1) and T2(col1, col2) which needs to be joined. But T2 may have col1 as null in which case col2 can be used as backup. What I want is either join on T1.col1 = T2.col1 or T1....
0
votes
0answers
21 views

COUNT() leaves me with one row only

I use the following INNER JOIN to get my values from SQLite tables books and book_authors: SELECT * FROM (SELECT book_authors.book_id, book_authors.author_id, book_authors....
0
votes
1answer
19 views

Simple Update Join much slower than it should be (MYSQL)

This is a simple Update Join that updates only about 100 rows: Update A INNER JOIN B using(id) SET A.active = 1 WHERE A.date > '2020' This takes about 30 seconds to run, despite the fact that: ...
-1
votes
0answers
12 views

Select a single record in a joint tables

The goal of the query is to find all customers who have a current invoice and populate the customer contact. I have 3 tables as follows: Customer Table Invoice Table Contact Table. The three tables ...
1
vote
4answers
155 views

Get results from “combined, either inner join”

I have a feeling this must have been asked multiple times, but I just cannot find a solution, or even a questions probably because of difficulty in explaining. We all know that inner join basically ...
-2
votes
1answer
15 views

Find items missing from table

Long time reader, first-time posting. This seems like a simple search but I can't find the answer I need, and the ones that make sense aren't giving me the desired result. I'm almost embarrassed to ...
0
votes
0answers
11 views

pulling most recent results for each point based on date field

so I have tried a few methods now mostly using max(date) and not really understanding row (in) and aggregate. I have two tables in which I am doing a join that requires some field formatting to ...
0
votes
1answer
99 views

SUM and JOIN 2 columns with different tables but it duplicates results

SELECT DISTINCT [Transfer Shipment Line].[Transfer-to Code] AS [Loc Code] ,[Transfer Shipment Line].[Item No_] ,[Transfer Shipment Line].[Description] ,[Transfer Shipment Line].[Description 2] ,[...
-2
votes
1answer
22 views

How can i add right join to other table in this code

I have this code runninh: SELECT IDLOTE, SUM(CASE WHEN FECHAMOVTO='2020-01-01' THEN cargo ELSE 0 END) *4 as saldo, SUM(CASE WHEN ...
0
votes
1answer
18 views

What is the best way to handle a join / group_concat query in MySQL

I am looking for some feedback regarding the best way to handle the following (simplified for this example) situation. I have 3 tables: tasks, assignees, and users ---tasks---- id, task, completed ---...
0
votes
1answer
28 views

Get top 5 students for each class in many-to-many relationship [duplicate]

So I'm doing this in MySQL. I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their ...
-1
votes
2answers
35 views

How to do left join , same table with different conditions per column

Hi i need information about how to merge both sentences Sentence 1 select idlote from polizamovtos group by idlote having sum(cargo)-sum(abono) > 0 Sentence 2 SELECT SUM(cargo) as saldo FROM ...
-1
votes
2answers
29 views

Do these joins change anything [closed]

I have a request which looks like this : SELECT A.* FROM A INNER JOIN B ON A.id_B = B.id INNER JOIN C ON B.id_C = C.id A.id_B and B.id_C are foreign keys NOT NULL For me the INNER JOIN ...
0
votes
2answers
56 views

LEFT JOIN not working as intended

I'm using mysql 8.0.21. I'm trying to join two tables such that I can find the index name of foreign keys. This is my query: SELECT KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA, KEY_COLUMN_USAGE....
0
votes
1answer
12 views

How to Fetch A Record If Shipping And billing Address are Different

Show New Orders with Address If shipping and billing are different. I am stuck with this query. Can someone guide me a bit. Thanks
1
vote
1answer
55 views

Multiple roundtrips vs cross joins

I have a feeling the answer is "it depends on...", but I'm wondering if there's a concrete answer. In Entity Framework, you build a query using C# code, and the framework converts to SQL and ...
0
votes
1answer
13 views

How to perform multiple joins onto a table and then join resulting table onto multiple columns into another table

| family_id | parent_id_1 | parent_id_2 | ----------------------------------------------- | 1234 | 9999 | 9567 | ----------------------------------------------- | ...
1
vote
1answer
29 views

Why Am I Getting Inconsistent LEFT JOIN Results in MySQL

Background I have a database in 3 different locations with the same exact static data (nothing updates it, it was a one-time import from another system). In 2 of the 3 locations, a query similar to ...
0
votes
2answers
52 views

Multiple group by with inner query in Oracle

I have a table Record with three columns: p_id, score, city. P_id score city 12 20 Mumbai 12 80 Delhi 12 50 Pune 13 20 Mumbai ...
0
votes
3answers
41 views

mySQL foreign key and primary key

Consider table A with columns id (primary key), name and table B with columns id, a_id (foreign key linked with table A id column), address. What will be the sequence of columns if the query is: ...
-2
votes
1answer
34 views

SQL JOIN query problem [closed]

I'm doing SQL class on Khan Academy and got stuck somewhere in the JOIN query section. Two tables: customers table with id(pk),name, email Orders table with id(pk),customer_id, item, price Question: ...
0
votes
1answer
120 views

Postgres optimizing join of 2 large tables

I have 2 tables one with around 1M entries and other around 4M. I want to join these tables in a materialized view. But the materialized view creation is taking too long ~4 hours. I don't see the ...
1
vote
1answer
50 views

MySQL SELECT slow, but only 2 x 300K rows and indexes

Have the MySQL SELECT query below that is awfully slow. It takes ~1.0 seconds to execute despite having only 300K rows and indexes, so I would love to find a way to get it to execute faster since it's ...
0
votes
0answers
12 views

What is the best relationship for a pair of tables that have optional dependencies but aren't really related?

I have two tables inspections and tasks. The inspection is allowed to have a single task but not many. Inspections don't need tasks and really there is no direct relationship. However, I have a link ...
4
votes
3answers
514 views

Is a join faster with 5 tables joined together Or 5 separate queries? [duplicate]

I have confusion that I already have in my application 5 tables say a,b,c,d,e and have relationship with each other like a.b_id is primary key of b and chain goes on. Now my question is that which ...
2
votes
1answer
41 views

Caveats of OUTER JOIN on nested JSON value

I'm writing a query which is supposed to find the elements from a list which DO NOT exist in the DB. My first attempt at this was to use a nested query where the first query fetches the ids, then I ...
0
votes
1answer
19 views

Get missing weeks by country

I've got one table called "Units_Sold" which has these fields: year, month, week, country, units and another table called "Dates" which has these other fields: year, week, month ...
5
votes
2answers
121 views

Does Postgres optimize this JOIN with subquery?

In Postgres 12, I have a table purchase_orders and one for its items. I'm running a query that returns PO's for a given shop and a sum of items ordered on each PO: SELECT po.id, SUM(...
0
votes
1answer
19 views

Left Join table with first match historically

What I want to accomplish is to join TableB to TableA where TableA.date <= TableB.date, but only the the value with the nearest max date to Table.B.date. Currently, what I get is all the previous ...
0
votes
1answer
10 views

Selecting non-aggregate column on 3 table inner join

I have 3 tables in a standard parent/child kind of "belongs to" relationship in postgres: donations --(belong to)--> campaigns --(belong to)--> organisations Each table has an id ...
-1
votes
3answers
49 views

How to aggregate in the WHERE clause referencing the subquery from the FROM clause

I got the following query so far: SELECT q1.ID, Content, Volume, CustomerID, runtot FROM ( SELECT ID, Content, Volume, CustomerID, SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS ...
1
vote
1answer
12 views

LIMIT on table with 1 to many

I have 3 tables (A, B and C) that connect 1:m:1. I need to JOIN the 3 tables and apply a limit not on the total number of records, but on the number of records used from table A. This means that ...
1
vote
1answer
35 views

GROUP BY multiple criteria with difference in row fields within a group

Everyone, I am struggling to find a solution for the following task: All users (1) that have a game (2) with their items count (3) and items price (4) are to be retrieved by a query => Display ...
0
votes
0answers
25 views

Multiple join criteria with different keys?

I am having a hard time formulating this question for a targeted search and so none of the results I am getting seem to describe my exact situation. I work in healthcare and our tables are enormous, ...
0
votes
3answers
33 views

What is wrong with this syntax of a multi-table DELETE…ORDER BY?

I have the following query: DELETE proxies, pxpl FROM proxies JOIN proxy_xref_proxy_list AS pxpl ON proxies.ID=pxpl.ProxyID WHERE pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR) ORDER BY pxpl....
0
votes
0answers
17 views

MySQL: Use a LIMIT for a JOIN table added

View The view of the app contains a page which shows a bunch of carousels (each carousel = a tag), each of each should contain 5 products: +-------------------------------------+ |Page ...
1
vote
1answer
35 views

CASE in WHERE clause - retuns a single-row result instead of 4 rows?

Everyone, I do not want to bother you with the whole tables schema so I will paste a couple of screenshot only so that my question becomes clear - I need to show all projects that are related to ...
1
vote
1answer
52 views

Why does MySQL consecutive JOIN give an unexpected result?

I had 3 tables which were Category, Product and Vendor. Then I tried to make a consecutive join 3 tables in 1 query to count the product for each category. If a product or it's vendor is deleted, it ...
3
votes
4answers
122 views

Slow performance when joining a small table and filtering out on a non-key column in MariaDB (MySQL)

I am fairly new to MariaDB and I am struggling with one issue that I cannot get to the bottom of it. This is the query: SELECT SQL_NO_CACHE STRAIGHT_JOIN `c`.`Name` AS `CategoryName`, `c`.`...

1
2 3 4 5
34