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