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
272 views

Poor performance on MySQL 5.7 on joined tables ( scanning wrong table ?)

I have a joined table (News, Publishers) with indexing. The query is working fine on MySQL 5.5. After I upgrading one of the server to MySQL 5.7, I start noticing high load, high CPU, and slow query. ...
0
votes
1answer
257 views

Drastic change in query speed when changing from Oracle 9i to ANSI outer join syntax

I am working on simplifying a query originally written for Oracle 9i. In the process I changed the join syntax from the Oracle 9i syntax to ANSI. The names of the tables and columns have been changed ...
0
votes
0answers
12 views

MySQL Query to get rank of a student of specific class divison or group

I just stuck in one area and I am glad if you help me out of this problem. My problem is similar to Get the rank of a user in a score table But here is twist you already mentioned how to use WHERE ...
0
votes
1answer
84 views

How to remove NULL values in multidimensional array?

SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid ,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as ...
0
votes
2answers
27 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 ...
0
votes
1answer
186 views

Delete query does not end

I have a following table with 1 million records : CREATE TABLE `activity` ( `id` int(9) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `subscription_id` int(10) unsigned NOT NULL, `...
0
votes
1answer
268 views

How to split a multivalued column, do a lookup and update another column?

This is a part of my previous question. How can I achieve the following for all the records in my table using a single query? SET @TeamMembers = TeamMembers from tblProjects where ID = '125' ...
0
votes
1answer
276 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
1answer
48 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 ...
0
votes
1answer
290 views

Oracle DB union two select statements which return multiple rows

I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more ...
0
votes
2answers
259 views

Access Web App query 3 Tables with aggregatios - Need a workaround

I'm designing an Access 2016 Web App for inventoring our computers. The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC. The computer-value in RAM ...
-3
votes
1answer
49 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....
0
votes
1answer
24 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 ...
0
votes
1answer
21 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 (...
0
votes
1answer
687 views

MySQL get previous row with inner join

In my query, I need to get the previous row with the current row and everything works fine here. SELECT *, (SELECT calendar_id FROM calendar AS sub WHERE sub.calendar_id < ...
0
votes
2answers
16 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 | ----------------------------------------------- | ...
0
votes
1answer
32 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 ...
0
votes
1answer
44 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 ...
1
vote
1answer
137 views

Left outer join to itself to find outliers?

I have a table of item types and the coordinates of the items, like this: ItemType | X -------------- 1 | 1 1 | 5 2 | 5 1 | 6 2 | 6 ...
0
votes
1answer
586 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 ...
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 ...
1
vote
1answer
561 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
1answer
34 views

Understanding JOIN Syntax

Given: postgres=# \d foo Table "public.foo" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | ...
0
votes
1answer
82 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
2answers
197 views

Subquery or Inner Join?

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions. SELECT t.id, t.date, t.amount, t.description FROM ...
0
votes
1answer
74 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, ...
1
vote
2answers
1k views

Mysql: Create a view with multiple self joins without duplicates in result

Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you. So i have a table entities_attributes_values where an ...
0
votes
1answer
37 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
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
69 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
23 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
33 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
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: ...
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....
1
vote
4answers
156 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 ...
5
votes
1answer
77 views

MySQL uses INDEX for optimizing ORDER BY with GROUP BY except when you add a JOIN

Question Why can't Query #2 use the same (car_trims.horsepower_peak) index to optimize the sorting of the rows as Query #1? The only difference between the two queries is the addition of the JOIN in ...
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] ,[...
0
votes
1answer
496 views

perform multiple conditional join on multiple tables

I have a situation where I need to get result based on conditional joins For example, SELECT CGI.SALES_BRAND_BSNSS_ID, CGI.SALES_BRAND_CD, NVL(MTRX. matrix_score_cd,'RV1') ...
-2
votes
1answer
16 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 ...
2
votes
2answers
77 views

Given the following tables/index/explain planning, why is the query so slow?

I have the following tables: CREATE TABLE trace ( uuid uuid NOT NULL, result text, ruleid uuid, previousruleresultid uuid, senderduns text, receiverduns text, eventid uuid, ...
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 ...
2
votes
1answer
560 views

Performance: where before or after join

I am doing a query like this: select * from table1 join table2 on table1.fieldjoin = table2.fieldjoin where table1.field = 'y' I would ask is this query equivalent in terms of ...
-2
votes
1answer
23 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 ...
3
votes
4answers
135 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
vote
1answer
3k views

Index is not used when joining two tables in a stored procedure

In MySQL 5.7, I have two tables: CREATE TABLE `periodholdings ` ( `stkcode` varchar(255) NOT NULL, `participantid` varchar(255) DEFAULT NULL, `participantname` varchar(255) DEFAULT NULL, `...
0
votes
1answer
21 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 ---...
1
vote
2answers
198 views

MYSQL Update data based on top 10 records of each user

I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of ...
0
votes
1answer
934 views

Costly HASH MATCH Aggregate

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan CREATE ...
0
votes
1answer
129 views

What exactly happens when optimizer_prune_level = 1?

This documentation link states that optimizer_prune_level Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. Can ...

1
2 3 4 5
34