Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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....