An SQL join clause combines records from two or more tables or views.
0
votes
1answer
20 views
join two tables, include one row from the parent table for each primary key
I've got a simple join over a primary and foreign key... but I need to include a row from the parent table in the results for each primary key as a sort of header row for the children. For instance, I ...
0
votes
2answers
39 views
Insert into a table selecting data from a sub query [on hold]
I would like to insert a few data into a table selecting data from a subquery having multiple joins.
I tried the following way:
insert into mytable (columns)
select * from
(select columns
from ...
0
votes
0answers
21 views
Display values of joined tables WHERE table 2 col equals something [closed]
I have 2 tables that handle asset management and I'm trying to create a report that lists each asset under its location (which is determined by the desk_id, referencing the location in table 2)
Table ...
0
votes
0answers
22 views
Many to many join taking only one value from table 2
DB-Fiddle
The resulting table should have the same number of rows as the first table while adding the column State from Table 2. This State value should be the highest SRC_DT value that is lower than ...
1
vote
1answer
22 views
SQL Server - specify build and probe tables to hash join?
I am observing a situation in AdventureWorks database where the hash join operator is building hash table using a larger input (296 rows) and then probing with a much smaller input (3 rows). I'm not ...
0
votes
1answer
24 views
Select sales from different stores and group by product
I have three tables (SalesTable, StoresTable and ProductsTable) and the structure is the following:
--StoresTable
StoreId Name
1 Store 1
2 Store 2
--ProductsTable
Id Description
1 ...
0
votes
2answers
56 views
why optimizer in Oracle database does not use index?
when I execute following queries see completely different result where is problem?
here I execute a simple select query which takes a few seconds to complete:
SELECT * FROM
PRODUCTION....
0
votes
1answer
39 views
Execute UNION and after LEFT JOIN in MySQL
I have 3 tables
cdc_vw_Marbetes_Catalogo_Exis
tblMarbetesAdicionales
tblMarbetesCongeladas
First I make a UNION of the tables cdc_vw_Marbetes_Catalogo_Exis with tblMarbetesAdicionales.
(SELECT ...
-3
votes
2answers
29 views
LEFT JOIN returns NULL in middle of query [closed]
Started working recently with joins again and I am trying to get the server name from a different table with matching ID's (Foreign Key).
SELECT Website.`services`.`port`,
Website.`services`.`...
0
votes
0answers
21 views
MySQL INNER JOIN issue
I have this query that updates my table:
UPDATE mancanti_java a
JOIN from_generale_java b
ON a.commessa=b.commessa
AND a.modello=b.modello
AND a.stagione=b.stagione
SET a.fabrica=...
0
votes
2answers
31 views
Speed up slow query which uses ifnull, if, sub-query, join and order in MySQL
I have one SP which generates a final query (like the one below) which is taking too much time to execute.
Basically it has the following parts:
IFNULL to check that if value is null then send ...
1
vote
1answer
31 views
SQL squash rows returned when joining several tables
I have a database which has a table for the main items, lets call it A with primary key 'id'.
Then there are a number of tables (B, C...), each corresponding to some attribute that each item can have ...
1
vote
1answer
27 views
Delete from table using FK columns, JOIN and LIMIT
This query below will never complete, since it always looks at the first 100,000 (user_type, user_ids). How can I write this to delete 100,000 records from the table with the FK reference (Product) to ...
0
votes
1answer
27 views
How to UPDATE from JOIN with GROUP BY
In a SELECT query of
SELECT b.id, MIN(IFNULL(a.views,0)) AS counted
FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id
HAVING counted>0
How can I turn this query to UPDATE as
UPDATE b.number ...
1
vote
1answer
23 views
“Print” specific columns through foreign keys
I have three tables:
Users
Users_request
Users_applications
Table Users has the id column, that column is related on Users_request.id finally Users_request.id is related with Users_applications.id
...
0
votes
0answers
28 views
Overwriting foreign key column values in JOIN statements
The foreign keys from referencing table and from original table are overwriting themselves in SQL JOIN's results. And that is happening in the order they appear in the SELECT list, later one prevails. ...
0
votes
0answers
24 views
How to join 2 tables on a Month to get the IDs of T1 that are in T2, and the empty rows of T2?
Set-up: PHP 5+ / MySQL 5.5 / 2 tables
T1 : id_1 / date_1 / hour_1 / id_1_s / ... (date/time of a course)
T2 : id_2 / id_2_s / name_2 / month_2 / ... (students who have been pre-selected for a course)...
-2
votes
1answer
56 views
Is there any ways to get the row count of joined tables?
Is there any methods to get the row count of the table which is related to the specified condition.?
here is my query
select *
from table2 t2
inner join t1 on t2.table1_id = t1._id where t1....
0
votes
2answers
30 views
Select Sum from two joined tables
There are structures:
CREATE TABLE `invoices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `invoices` VALUES (1,'2018-09-22')...
0
votes
0answers
29 views
Trying to join two tables and get results within a date range of a week
I have two tables, users and scores
Users
+----+--------+
| id | name |
+----+--------+
| 1 | John |
| 2 | Jane |
| 3 | Clarke |
+----+--------+
Scores
+----+---------+-------+------------...
0
votes
2answers
45 views
UPDATE with JOIN: Incorrect syntax near the keyword 'RIGHT'
I'm using SQL Server Management Studio 2005. I want to look up a column value in another table, using a shared ID tblOrder_ordernr in both tables, but only update one table.
To me, the query is ...
0
votes
0answers
69 views
How to join each row only once when destination rows overlap
Here is my test case:
CREATE TABLE tmp.src AS
SELECT * FROM (VALUES
(1,1,null),
(2,null,null),
(3,1,0)
) t (...
0
votes
1answer
36 views
Need to create join on multiple ids on same cell
I have a table uploadedFiles which I have a column assign_users_id which has multiple users in a single cell:
+---------+-------------+----------------+
| File_id | file_Name | assign_users_id|
+---...
2
votes
1answer
19 views
Get row with max date from a joined table
I have this schema of dossiers having 1 to N pages, and each page has a date:
CREATE TABLE dossiers
(id INT AUTO_INCREMENT,
code VARCHAR(50),
PRIMARY KEY (id)
) ENGINE=MyISAM;
CREATE TABLE pages
...
0
votes
0answers
12 views
Identifying time difference between repeat rows
I have a table of time entries from customers which logs an identifier.
I want to get a view of how many customers repeat contact based on a particular outcome, allowing me to see the repeat rate, ...
0
votes
1answer
26 views
How to join 2 tables when calling a count on the second table
I have 2 tables. 1 is called coaches, the other coach assignments. coach assignments keeps track of what coach is assigned to a customer. When new customers come in I want the coaches with the fewest ...
1
vote
1answer
83 views
Full Outer Joins Slowing Down Query
I am relatively new to SQL, so I apologize if I ask some basic questions.
Our database is setup on Azure, we have automatic indexes turned on. The query I have takes about 6 seconds and gets 2217 ...
0
votes
1answer
31 views
Which join to use ? and how to add rank column on the result [closed]
I have two tables
Table4 - Columns (ID, NAME, Money)
Table5 - COlumns(ID, PLACE)
Note: Table 4 has more IDs than table5
I am trying to use a join such that I get the following table
Table6 - ...
0
votes
1answer
33 views
Select records based on earliest date in a linked record
I am a retired teacher working on my first MYSQL database as a personal project; please let me know if I've inadvertently violated conventions. And thank you!
The database's domain is fiction stories,...
0
votes
0answers
39 views
Database Design - Meal (Combo) with multiple customizable items
I am working on a project where a customer is able to customize his/her combo meal that contains multiple customizable food items.
I have created a "Meal" table joined with "Items" so that multiple ...
1
vote
1answer
42 views
Is there a way to speed up JOIN ON LIKE?
I have millions of things in a table. I'd like to assign groups to the things based on theirs prefixes.
I tried to add all the indexes but the planner still does nested loop left join.
A demo case ...
2
votes
1answer
15 views
How can I pivot a table then join multiple tables and change values based on information between tables
The database I'm using has multiple tables storing different information types...it's very convoluted.
I'm trying to join this information into one table, with all of the details for a single ...
2
votes
1answer
49 views
Why does MySQL optimize query with “WHERE … OR” so badly?
Hey there,
this is my first question so please be patient if I miss to add something in here.
My question is why the OR in the WHERE clause causes the query to be executed the way it is (which is ...
1
vote
0answers
42 views
Efficiently query product variants from inventory database (avoiding EAV)
I'm building an ecommerce website for fun with three main components: frontend in Angular, MySQL database and a PHP backend that will function as a REST API.
The inventory database consists of ...
1
vote
0answers
27 views
Index to facilitate joining a table against itself to gather the last observation
I have a discrete step timeseries table where I insert about 1M rows per "month end" with the identifier being the last day of the month (2018/02/28 or 2018/08/31). I try to keep as many months as ...
6
votes
1answer
103 views
SQL Server chooses Nested Loop join with dimensional table and make seek for each row
I face an issue where SQL Server generates a un-optimal execution plan: a
Nested Loop join and seek to the dimensional table and executes 2M reads on it.
The Sort operation Estimation is 100 rows ...
0
votes
2answers
36 views
How to SELECT multi-columns from a column?
I have a table with the structure
book_id int(11),
section_id int(11),
note_id int(11),
item_number int(11),
text varchar(255),
UNIQUE(book_id,section_id,note_id,item_number)
item_number can be 1 or ...
9
votes
1answer
166 views
Why doesn't join elimination work with sys.query_store_plan?
The following is a simplification of a performance problem encountered with the Query Store:
CREATE TABLE #tears
(
plan_id bigint NOT NULL
);
INSERT #tears (plan_id)
VALUES (1);
SELECT
T....
0
votes
1answer
30 views
How to get a selection from a join, where there is no result in one of the tables
I am working in an oracle database and looking to get some queries to identify any changes of address from a customer. I am creating a report from the results in a daily batch process. I will simplify ...
1
vote
1answer
21 views
JOINing 3 tables with ORDER, LIMIT and WHERE
I am trying to join these 3 tables by key organization_id but I had no success.
What I'm trying to achieve: I know ipo and I want to get some values from name_entries and address_entries.
First query ...
2
votes
1answer
51 views
Speed up SELECT query with JOIN from table with 2.2M rows in PostgreSQL?
There are two tables in PostgreSQL:
//has 2.2 millions rows
create table account (
account_id text primary key,
email text not null,
created_at timestamp not null
);
//has 1.8 millions ...
1
vote
0answers
26 views
How to optimize this Postgresql query
Table A has columns (P1, P2, Value1) where P1 and P2 are a multi column primary key.
Table B has columns (P3, P1, Q1) where P3 is the primary key.
Table C has columns (P2, Q1, Value2) where P2 and Q1 ...
0
votes
1answer
16 views
select data from related tables and display their counts in the relating table
I have a table which stores data about an inspection. Inspections are carried out in a particular company belonging to an industry, located in a region. the inspections table has the industry_id field ...
1
vote
2answers
119 views
How to run update query on multiple table via joins simultaneously on postgres?
In MySQL, I can do a query like this
UPDATE
prd_sectionshapename se
inner join
prd_shape s
ON
s.id = se.shape_id
SET
se.company_shape_name = 'ABC',
s.name_en = 'Another name'
...
0
votes
1answer
34 views
How to get the consolidated ServiceID with the sum of File Size associated with over an year
Can anyone please help me on below to get the consolidated ServiceID with the sum of File Size associated with it over an year.
Complete Code (For reference)
If(OBJECT_ID('tempdb..#temp1') Is Not ...
0
votes
1answer
33 views
CROSS JOIN with table that has no row
In definition;
SELECT *
from T1, T2
Cross Join: If T1 has N rows, T2 has M rows, the result set will have N x M rows.
If T2 has no row, then result of the JOIN has no row also.
However, I want ...
0
votes
2answers
33 views
Join tables with some same values
I'm trying to get lines that have same ADAPTERID and Attribute_Name, but Attribute_Value can be active or designed automatically.
I used:
SELECT * FROM database1
where Attribute_Value IN ('ACTIVE','...
0
votes
2answers
53 views
LEFT JOIN with WHERE (Microsoft Access SQL)
Given following 2 tables a, b:
table a
ID job
1 farmer
2 physician
3 bank clerk
table b
bID ID lang job
1 1 en farmer
2 2 en physician
3 3 en bank clerk
4 2 de Arzt
5 1 ...
1
vote
1answer
19 views
extract different number of rows from multiple tables
I have a quick question. I'm looking for the concept behind this, not necessarily a ready-to-use code, but if it's not too complex, I don't mind a quick solution. I have an app that manages accounts. ...
0
votes
1answer
25 views
Extend Query to include additional column
I have the following query, which gives me (in addition to other columns) the Direction ID only:
SELECT Users.Name, Users.Surname, Transactions.Timestamp, AccessPoints.Description as Door, Readers....
