Stack Exchange Network

Stack Exchange network consists of 175 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
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [join]

An SQL join clause combines records from two or more tables or views.

0
votes
0answers
17 views

Query is faster with additional JOIN

SELECT COUNT(DISTINCT User_id) FROM Session GROUP BY Floor_id; SELECT COUNT(DISTINCT User_id) FROM Session JOIN Floor ON Floor.id = Floor_id GROUP BY Floor_id; The second query runs 20x faster ...
0
votes
1answer
15 views

MySQL - LEFT JOIN with ORDER BY is slow

I have three tables in MySQL - gallery, gallery_likes and gallery_info. I want to obtain photos from gallery based on the folowing query: SELECT g.photo_id, g.name FROM gallery g LEFT JOIN ...
-2
votes
1answer
14 views

How to retrieve a users uploaded files using foreign key

I have two tables,users, and,files, Users Id,firstname,lastname,email,password files Id,filename,description,userid Userid in files is foreign key witch relates to Id in users I am trying to ...
0
votes
0answers
16 views

mysql query with 1 derived table and 1 inner join takes forever to load

I tried removing some chunks of code little by little and found out that the condition inside the inner join is the devil. Can someone enlighten me on how to achieve this when I have like 10,000+ rows ...
0
votes
1answer
10 views

Help with optimizing Oracle Query - nested with count

I have 2 tables (Students and Cellphone). Columns in Students: StudentID, FNAME, PHONEID Columns in Cellphone: PhoneID, Phonename I want to create this: • Run a query to show the most popular ...
0
votes
0answers
36 views

SQL Server group by date, time between status

I have a table that looks like the following: id_usr dt_event Sequence 0000001 2015-08-01 14:57:21.737 1 0000001 2015-08-06 15:34:21.920 2 0000001 ...
0
votes
0answers
42 views

postgres: join tables, order by GREATEST

Having the following table structure table1 id: BIGSERIAL updated_at: TIMESTAMP WITHOUT TIME ZONE DEFAULT now() ... CREATE INDEX table1_updated_at ON table2(updated_at); table2 table1_id: ...
0
votes
1answer
17 views

Cannot figure out how to remove this Using Temporary

I have what seems like a simple query, and my indexes seem to be sufficient without the INNER JOIN, but after I add the join, I end up seeing "Using Temporary". Would also appreciate any good ...
0
votes
1answer
18 views

specify the correct element to pick in JOIN mysql

i Have three tables: --------------------- | PRODUCTS | --------------------- | id | product | --------------------- | 1 | super prod 1 | | 2 | super prod 2 | | 3 | super prod 3 | |...
0
votes
0answers
12 views

How to find full siblings? I have been able to find the parents, but can't figure out how to join siblings based on having same parents

I have two tables (table/column names not my choice): | Person | | Parent | ------------------------------ ----------------------- | ...
0
votes
0answers
46 views

Combine 2 rows of query results into one, with all columns filled (the two rows have opposite columns filled)

(Oracle SQL) I have been trying to figure out this problem on and off for a couple of days now, and it is driving me nuts. I don't have much experience with joins, or SQL in general, but I have ...
0
votes
0answers
36 views

Can Mongo Handle This SQL Aggregate Query? [closed]

I have this SQL query which runs through 10 million rows in about a second and produces this output: |---------------------|------------------|------------------| | LabID | Matched ...
-1
votes
0answers
43 views

Full outer join not pulling all records from right table [closed]

I am attempting to pull all rows from the left table as well as the right table with the following example query. However, there is one row from the right table that is not being included because it ...
1
vote
0answers
79 views

JOIN query ON column alias [on hold]

I have a query which has a column alias created with a CASE statement, and I am trying to JOIN a subquery onto this. I have managed to do so using OR statements in the JOIN, however the CASE ...
1
vote
1answer
102 views

MySQL - Creating sort index and update query takes forever

I have the following MySQL query UPDATE PCL AS p JOIN HST AS h ON h.SrNo = ( SELECT hi.SrNo FROM HST AS hi WHERE hi.SYM = p.INST AND hi.DATE >= p.Date AND hi.HP >=...
0
votes
3answers
46 views

SQL Join and Count

I have 3 tables in SQLServer that I want to join so I can count the number of times each SUBJ_NAME occurs for the ITEM_IDs in the LOAN table. CREATE TABLE SUBJT ( SUBJ_ID CHAR(4) PRIMARY KEY, ...
3
votes
0answers
29 views

Block nested loop join cost

What is the cost of block nested loop join? Given B1 blocks of R1 (outer relation) and B2 blocks of R2 (inner relation), why does it cost B1+B1*B2?
3
votes
2answers
268 views

Get top 1 row value from third table while joining 3 tables mssql

I am new to mssql .here I need to get some data using joins between three tables . Header join Lines join Images --> Result Header Table : Line Table : For each header record we can have multiple ...
0
votes
1answer
25 views

Subtract values in same column from 2 different dates in same Mysql table

I have a MySql table like: Item Date Likes Book 2012-05-20 402 Book 2012-05-21 432 Book 2012-05-22 450 Pen 2012-05-20 20 Pen 2012-05-...
0
votes
0answers
11 views

Join hive tables which are being populated in real time

I have two tables in hive which are being populated in real time from a set of files. There is a request_time and trx_id columns in both tables where trx_id is the joining key. I want to join them ...
0
votes
2answers
33 views

Poor performance on a 'leaderboard' query

Consider the following 2 tables: players: | rooms: id | id name | type room_id | embed_code_id points | My query needs to fetch the 100 players with the most points from a given ...
0
votes
0answers
37 views

MySQL how to GROUP SUM based on JOIN TABLE

I have Three Table, One Master Table and Other Two Table have relation with it. Below is the Three Table: 1. ball_by_ball table, this table holds full information by ball by ball: CREATE TABLE `...
1
vote
1answer
38 views

Why Output is wrong after doing the left join as subquery

I have TWO QUERY which work perfect, but when i join those two query as LEFT JOIN the output become wrong. So what is the problem.? Query 1: select Batsman.innings_no, bowler, sum(...
1
vote
1answer
36 views

Left join that has more than 0 matches [closed]

Let's say that in the on clause of a left join (a on b), some products of a and b satisfy the on clause and some don't. Will the products that do not satisfy the on clause be included in the result ...
2
votes
3answers
49 views

How to group many information une one select

There are my database articles id | art_code | art_name 1 | 5678 | computer 2 | 0987 | display 3 | 8547 | keyboard action id | code | action | qte 1 | 5678 | IN ...
0
votes
1answer
38 views

Complex join 3 tables with sum and group by

I'm trying to generate a report for one of our departments that pulls data from 3 tables. The basic structure is below: CREATE TABLE `evd_us_county_mio_2018q4` ( `state_code` INT(11) NULL DEFAULT ...
0
votes
1answer
42 views

How to join two columns and find unique combinations

I have the following topological structure (topo) with their types: id(string) | id_from(string) | id_to(string) | type(string) '111' | 'aaa' | 'bbb' | 'type1' '222' ...
1
vote
2answers
38 views

Inner join only for not empty values

i can get right result using the where join , with next sql: WITH fktable1 AS ( SELECT 2 AS id ) ,fktable2 AS ( SELECT 3 AS id ) SELECT main.* FROM -- main data ( VALUES (1, NULL, NULL), ...
1
vote
1answer
22 views

Count Assets Barcodes and Group by Rooms

I have 2 tables Asset Register and LocationRoom. I want to count the number of asset barcodes in each room. I want the output to be the list of Asset Barcodes, Description,RoomNo and count for ...
1
vote
1answer
33 views

IN subselects vs INNER JOIN (performance)

I have to make a choice: choice 1: using IN subselect choice 2: using INNER JOIN See the examples: IN subselect: SELECT contractServiceCode FROM contractServices WHERE contractID IN ( SELECT ...
0
votes
1answer
53 views

Postgres select join json querry

I have a problem to make a postgres query with join from data of json. I have 2 tables the first one is media: id as integer path as string and an other table playlist id as integer media as ...
2
votes
1answer
29 views

JOIN Call table to grouping by Origination and Destination

It's a call system. tables structure and data sample : tbl_countries CREATE TABLE tbl_countries ( country_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, country varchar(100) NOT NULL ); ...
0
votes
0answers
14 views

Variation on a join table

So a join table for many-to-many is typically(?) a two-column table with foreign keys to two other tables. I have a colleague that insists on putting an FK in two tables that are FKs to a "join" ...
0
votes
0answers
50 views

PostgreSQL select jsonb key value from an array while joining table

I want to extract a string value from an object's key inside a jsonb array, coming from a table join_table which is left joined, meaning it could be empty for some values. -- NOT WORKING SELECT ...
1
vote
1answer
21 views

Formatting query to respond to past column values

I'm writing a query that follows the process of forms being processed through various SQL functions - they receive status numbers as they go through the process. I want to pull forms that are in a ...
0
votes
1answer
14 views

Duplicate results from query trying to find cross-indexed multiple tables

I working with a database of several hundred schemas with 250 tables and thousands of records. PGAdmin III 1.22 as my only tool. A GUI interface to the database is not robustly written and ...
2
votes
0answers
51 views

Huge speed difference between left join and inner join - Postgres

It is known that left join is generally slower than inner join, but this difference seems out of proportion. I have table A with 2,542,526 rows and table B with 30,444 rows. I ran this query, which ...
0
votes
2answers
23 views

Inner join or cross join Confusion

For example select * from a inner join b on a.i=b.i ; is definitely inner join. But can you let me know whether the below statements belongs to cross join or inner join ? select * from a ,b ...
0
votes
1answer
20 views

Count Total Transaction each specific row even if 0

I have a 3 Columns namely: Payment Channel Payment Name Total Transaction The Total Transaction will be the COUNT of each Payment Channel and Payment Name. In the Payment Channel, values have PC01-...
1
vote
1answer
28 views

SQL Outer Join while conditioning on an extra column

I am trying to pull up an outer join between two tables, on a specifc column "Identifier", however, I would only like to carry out the outer joins on the rows where additional column "Date" matches ...
1
vote
1answer
43 views

How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient

I have three tables (A, B and C) with various data regarding mining facilities in them. But only one table (C) has two columns with coordinates. My final goal is to produce a table which pulls similar ...
-1
votes
1answer
42 views

Query joining four tables resulting in one row

I have these two queries which pull data from four tables: select files.id, files.name, dirs.dir from files inner join dirs on dirs.id = files.idDir where files.id = 45 select label from labels ...
0
votes
1answer
35 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 than ...
0
votes
0answers
30 views

Comparing count of 2 tables, is this the correct way?

I'm building a survey system where A client that has many users A user can have multiple surveys. I have a table users and a table survey_completions, I'm wondering if this is the correct (or best) ...
0
votes
2answers
29 views

How to SELECT most common (viral) from a table and LIMIT results for pagination?

Sorry if it's a dubplicate but I can't find an answer to the question. Hello there so I have three tables users and articles and likes, a user can write an article, in the table articles there is ...
0
votes
1answer
94 views

Full Outer Join not working as expected [closed]

I want to make a view from these tables. The first inner join works as expected and brings a new column with AuspraegungID from which I want to connect to the ArtikelVarianten table At the moment I ...
1
vote
3answers
89 views

How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql

I have legacy tables similar to the following: employee ------------------------------------ | employee_id | name ------------------------------------ | 1 | David | 2 ...
1
vote
2answers
63 views

Exclusionary Left Joins

I'm trying to join 2 different tables (Table99 and Table2) to Table1. The first is a straight-forward RIGHT JOIN. The second is a LEFT JOIN, where I'm trying to take Table1 and Left Join to a Table2,...
1
vote
0answers
34 views

Using conditions to return specific rows

From the following table i am trying to retrieve specific rows based on a criteria +----+--------------+------+ | ID | UPDATED_DATE | TYPE | +----+--------------+------+ | 1 | 01/13 | P | |...
0
votes
2answers
27 views

Mysql query to fetch records which are not matched form both tables

I am new to here. I have two two tables , one is temporary table and another one is main table.Before doing final process user click on refresh button . Then I am populating data into the temporary ...