Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
2,001
questions
0
votes
2
answers
99
views
JOIN and GROUP BY with additional columns
If I run the following query on my database I get the results pasted below:
SELECT t.id as ID,
j.text as Text,
l.location as Location,
FROM [text] t WITH (nolock)
INNER JOIN Text_Location tl WITH (...
2
votes
1
answer
319
views
sort results by highest performing country & region
I want to check which country & regions are performing the best in sales.
I have the following order table:
| order_id | amount |
|----------|--------|
| 100 | 1000 |
| 101 | 250 |
...
0
votes
1
answer
27
views
How to get desired result set?
I have the following two tables
customers
| customer_id | firstname | lastname | email |
|-------------|-----------|----------|----------------------|
| 1001 | John | Doe ...
3
votes
3
answers
8k
views
Can I speed up a large MySQL/MariaDB join through partitioning or merging?
I'm trying to run the following join query as part of a more complex one on MariaDB 10.1.26.
select distinct
project_commits.project_id,
date_format(created_at, '%x%v1') as week_commit
...
-1
votes
1
answer
4k
views
how to select query to inner join same column twice?
Table A: Col1 [TicketSubject] Col2 [Createuserid] Col3 [Modifyuserid]
Table B: Col1 [ID] Col2 [Name]
How I imagine it would work
SELECT a.ticketsubject
,a.createuserid
,a.modifyuserid
...
-2
votes
1
answer
8k
views
Varchar Overflowed an Int Column [closed]
I am having an error with the following query:
SELECT scp.phone, scp.extension, scf.fax, sce.email
FROM supplier_contacts AS sc
LEFT OUTER JOIN supplier_contact_phone AS scp
ON sc....
0
votes
1
answer
3k
views
Join Tables without losing Data
I am in a situation where I need to query a database for data, but am struggling on how to properly form the query. Essentially I need to get data from the following tables:
Table: Contact
...
0
votes
1
answer
78
views
JOIN by part of two column names
Consider a table with the structure of
id col1
WqDs.first
WqDs.second
eeRT.first
eeRT.second
How can I JOIN the table to itself using the first part of the id
SELECT idxx, a.col1 AS ...
0
votes
1
answer
4k
views
Inner join on output of a subquery
We have Slot table which tells us about freetime for an employee between starttime and endtime.
We need to find Slot which have freetime > 'x' mins. This could be either be a complete slot or it can ...
-2
votes
2
answers
573
views
Nontrivial joining a relation with itself [closed]
I am a philosopher and am doing my first steps in relational algebra. So here is a puzzle:
Let say that we have a relation R with attributes 'R.a' and 'R.b'. Is it possible to make a renaming and ...
-1
votes
1
answer
47
views
How to combine results from three tables where two tables might have multiple records matching the criteria
I have three tables:
mysql> select * from contacts;
+------+------------------+-------------+------------+------------------------+---
| c_id | c_username | c_firstname | c_lastname |
+------...
4
votes
1
answer
2k
views
How to index two tables for JOINed query optimisation
I'm using PostgreSQL 9.5 on High Sierra.
Over the two tables:
request_logs - ~ 26K rows
response_logs - ~ 9K rows
I've the following query (with JOIN):
SELECT
req.uuid,
res.status,
req....
-2
votes
2
answers
2k
views
11 LEFT JOIN s make query very slow
I have this big query it contain 11 LEFT JOINS . when i run it. it get 12s to return result . is some one can assist me to make speed this query that big help. um new to ...
-1
votes
1
answer
161
views
How to link two columns under new column in the same table?
I want to link two columns under new column in the same table. For example: in my database there is an ID of the employee and there is another ID for his manager in the same table. Also, in the same ...
2
votes
0
answers
149
views
MariaDB running indefinitely on inner join with count
I am currently experiencing a very, very weird issue with a query in MariaDB. Our server experiences a "504 Gateway Timeout" quite often after a database upgrade, and I was investigating the issue.
...
1
vote
2
answers
5k
views
Select count without join
I'm using MySQL. I have three tables, and in every table, I have a time column.
With a single query I want to count the number of rows that are between two given dates.
I have no need with a ...
1
vote
2
answers
1k
views
How to use GROUP BY in an efficient way without losing attributes? [closed]
I am working with PostgreSQL.
My idea is to group players who appear more than 3 times in table-a.
table-a = <player_id, year_world, c, d, e>
With the first subquery I get player_id and the ...
4
votes
1
answer
5k
views
How do I do a JOIN in ANSI SQL 89 syntax?
In Oracle, I use
SELECT * FROM table1...JOIN...
where the dots represent either the type of join or the condition on which to be joined.
Is this ANSI 89 syntax? If not, then how would I perform a ...
3
votes
1
answer
90
views
What is better join, in or in with join
I have these tables:
calls: rows [ 'created_at', 'user_id']
users: rows ['id', 'username']
And I need to get all user without calls from date:
How can I get it? Or what way is better and why?
I ...
2
votes
1
answer
1k
views
Function in JOIN condition
I have big dynamically constructed query with
FROM main_table t
LEFT JOIN table1 t1 ON my_func(t.col1, 1) = t1.col1
...
LEFT JOIN table6 t6 ON my_func(t.col1, 6) = t6.col1
Query is very slow, but if ...
7
votes
1
answer
5k
views
Optimising expensive join/subquery by filtering with conditionals
This is a question about short-circuiting expensive JOINs or sub-queries in Postgresql (9.5 or 9.6). I'm also be interested in hearing how people in general solve the check-then-execute problem.
I'm ...
1
vote
1
answer
105
views
Question concerning minimal/cannonical cover
For the question above, I am confused with how to approach simplifying the left hand side of BDCE->A. Do I get rid of E, as D depends on it, or do I get rid of CD as well, as CD->A?
Here's what I ...
1
vote
0
answers
856
views
Avoid adding an id-only table for a one-to-many table
I have a table that links user IDs with external identities (one-to-many) and I would like to avoid creating a second table with just IDs.
Simplified example:
CREATE TABLE identities (
uid ...
1
vote
1
answer
642
views
greatest n per group with a 3 tables join
Working on a project with following objects:
People
id
Vehicles
id
model: text
and a link table for owners which passes through owners changes
Owner_Changes
old_person_id
new_person_id
...
1
vote
0
answers
42
views
How to make use of indexes with inner join in Postgis?
I got 2 sets of points in 2 separate tables. Table_a got 100k points and table_b got 300k of points. I trying to find nearest points in relation find me any point from table_b that is within 50 meters ...
0
votes
1
answer
3k
views
Joining two tables with unrelated where clauses
I have two tables: (EMPAssignments) and (EMPloyees). Both have an employee id column.
EMPAssignments has columns for employeeid, assignment, and ftepercentage:
employeeid assignment ftepercentage
...
13
votes
1
answer
3k
views
Why is PostgreSQL choosing the more expensive join order?
PostgreSQL using defaults, plus
default_statistics_target=1000
random_page_cost=1.5
Version
PostgreSQL 10.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit
I've vacuumed and ...
2
votes
2
answers
2k
views
Understanding Variations on Multiple Joins
I have two versions of a multiple join which produce identical results. The five tables are:
customers ← sales ← saleitems → paintings → artists
The arrows (hopefully) show the relationship between ...
0
votes
1
answer
77
views
SQL Join vs NoSQL Wide column
If we need to have 2 tables, one storing the author info(author_info), & another storing all the books info(book_info).
author_info has authorId as it's primary key, whereas the book_info table ...
0
votes
1
answer
2k
views
Unable to run FULL OUTER JOIN
Here is my query .
SELECT user_record.password,user_addresses.street
FROM `user_record`
FULL OUTER JOIN `user_addresses`
ON user_record.id=user_addresses.user_id
ORDER by user_record.id
...
-1
votes
1
answer
800
views
a RIGHT JOIN is not working as expected
Question UPDATED:
Yes I know the tables are not normalized and yes I know there are no primary keys.
Semester table
Semester, Year, City
1|2017|Washington
2|2017|Paris
3|2017|London
1|2018|Paris
2|...
1
vote
1
answer
73
views
How to make this query simpler
SELECT IH.InvoiceNo,
C.Name AS NameofCustomer,
IP.Amount AS CashAmount,
0 CreditNote,
0 ChequeAmount,
0 Credit,
IP.Amount AS SalesValue
FROM InvoiceHeader IH
LEFT JOIN ...
0
votes
2
answers
2k
views
Join two queries
I have two queries and I want to join these two queries by CUSTOMERID
Query 1:
SELECT [CUSTOMERID], ISTAGBLOCKED,MONTH([CREATEDDATE]) AS MONTH, YEAR([CREATEDDATE]) AS YEAR
FROM [ISSUER].[HISTORY].[...
1
vote
2
answers
42
views
Not able to make a query that give me the result that I want in MySQL with 4 tables junction [closed]
I need help to adjust my query to be able to have the result show in bottom.
This is my DB illustration:
And this is the command CREATE TABLE for each table:
CREATE TABLE `produits` (
`sku` ...
1
vote
1
answer
272
views
PostgresSQL default statement for join
I have a transactions table, each transaction has an address field which references a row in an address table, each address in the address table has a coinID.
I want to get a sum of all transactions ...
11
votes
2
answers
37k
views
Rule of thumb for tmp_table_size and max_heap_table_size MySQL properties
The title pretty much sums up the question itself: Is there a rule of thumb regarding the values for tmp_table_size and max_heap_table_size MySQL properties?
I've had a serious performance ...
1
vote
1
answer
38
views
How to select attribute from two indexes, both pointing at the same table?
I have two tables, one with this form:
table teams:
ID, IDPupil1, IDPupil2
1 3000 3001
and the second this form:
table pupils:
ID, Name, Forename
3000 Smith John
3001 Morgan Lisa
...
2
votes
1
answer
13k
views
How to create a view from a join with multiple duplicate column names?
I have a few tables with this sort of format
myTable: id | Table1_id | Table2_id | Table3_id | value
Table1: id | text | ...
Table2: id | text | ...
Table3: id | text | ...
I want to create a ...
1
vote
1
answer
34k
views
How to select two columns from two different tables using foreign keys from another third table?
I am using MYSQL and here is the schema that I have:
Here is my first table user
+-----+------------------+
| id | name |
+-----+------------------+
| 101 | Daniel Radcliffe |
| 102 | ...
1
vote
1
answer
409
views
Two Inner join on the same field to get same data
I'm developing a Report in SSRS for MS CRM 2011.
There is a table named StringMapBase which contains Option Set we have declared in different entities.
This table has Value and AttributeValue column.
...
12
votes
3
answers
5k
views
Fastest way to count how many dates' ranges cover each date from series
I have a table (in PostgreSQL 9.4) that looks like this:
CREATE TABLE dates_ranges (kind int, start_date date, end_date date);
INSERT INTO dates_ranges VALUES
(1, '2018-01-01', '2018-01-31'),
...
2
votes
2
answers
8k
views
How to use FOR UPDATE with a view that is built using LEFT JOIN?
Assuming a simple table, e.g.
\d+ task
Column | Type | Modifiers | Storage |
-------------------+--------------------------+--...
0
votes
1
answer
1k
views
Changing numeric type slow down query big time for postgres
I have 3 tables, leads, lead_addresses and addresses. Scheme looks like this:
recently we decided that foreign keys would be beneficial for us so we changed data types for lead_id and address_id from ...
1
vote
1
answer
14k
views
If else statement on join query
i am working with a Postgresql database
having two tables:
professionals[professional_id,user_id,account_id]
users[user_id email]
accounts[account_id email]
professionals
[professional_id,...
3
votes
2
answers
2k
views
Short circuit in JOIN
I've been searching everywhere but I have not been able to find anything about it, seems like everyone is concerned only with WHERE and CASE. (and I haven't a SQL Server at hand reach to verify it by ...
1
vote
2
answers
3k
views
How to select students that failed at least at all courses that student with id = 1 failed?
I have the following tables:
STUDENT (student_id, first_name, last_name, birth_date, year , domain)
PROFESSOR (professor_id, first_name, last_name, birth_date, hire_date, title, salary)
COURSE (...
1
vote
1
answer
967
views
How does MariaDB handle joins on ENUMs?
I'm just wondering because MySQL ENUMs are essentially tied to the table, how do they handle JOINs if two tables have the same ENUM? Are they join based on the ENUM value's or the string-key (input)?
...
14
votes
1
answer
10k
views
Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?
I have the following tables (taken from the Sakila database):
film: film_id is pkey
actor: actor_id is pkey
film_actor: film_id and actor_id are fkeys to film/actor
I am selecting a ...
0
votes
2
answers
76
views
How to bring data from two tables that could/could not be present in one of them
Background: I have two tables, let's call them "purchaseTransactions" and "Products", the first one, holds all the transaction made by product, and the second one has all my products available, let's ...
5
votes
1
answer
367
views
Postgres JOIN strange behaviour
I'm just starting studying Postgres and I'm in a situation where, depending how I do the JOIN on my tables, the performance and plan output seems really strange.
Those are the table used with their ...