Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,668
questions
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 ...
