An SQL join clause combines records from two or more tables or views.
-1
votes
1answer
8 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 fr ...
1
vote
1answer
16 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
24 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....
0
votes
1answer
44 views
Inner Join until null on self-referencing tables
I have the following SQL Table:
OrganizationalUnit
ID - PK
ParentOrganizationalUnit - FK
Title
Department_Code (optional)
Division_Code (optional)
Person
ID - PK
...
33
votes
2answers
935 views
Why does changing the declared join column order introduce a sort?
I have two tables with identically named, typed, and indexed key columns. One of the them has a unique clustered index, the other one has a non-unique.
The test setup
Setup script, including some ...
0
votes
3answers
50 views
Combine same field from multiple rows into one row
I am joining two tables HR and UserDefined based on the Employee_ID:
HR Table
First Name Last Name Employee_ID
Joe Smith 456654
User Defined Table
Area ud_1_text ud_2_text ...
1
vote
0answers
33 views
Finding the different rows from two resultsets based on one of the resultsets (MINUS, NOT EXISTS)
I'm testing queries on two resultsets that have the same value in certain columns on Oracle 11g. I want to exclude the rows that share these columns from my final result based on the first table. Say ...
1
vote
1answer
64 views
Inner join with greater or equal VS where condition using a variable
Suppose I have a table with a unique column and this columns has only integer numbers.
Eg: ID column with rows from 1 to 999.
My intention is to return only rows when my ID is greater than 500, for ...
1
vote
3answers
65 views
Mysql complicated join
I have two tables
mysql> select * from emp;
+----+-------+
| id | name |
+----+-------+
| 1 | John |
| 2 | Mari |
| 3 | Sami |
| 4 | Kate |
+----+-------+
mysql> select * from dep;
+----+---...
1
vote
0answers
13 views
MS Access 2016 M:N relationships return multiple columns instead of multiple rows from a JOIN
Let's assume a data model like this:
Books could have multiple authors as well as authors could have written multiple books.
If a book and its authors are selected by the following statement
(this ...
1
vote
1answer
33 views
How to reference primary key when inserting in MySQL
I have a database containing the tables authors and books.
I don't know how to correctly assign the foreign key in the books table. I read the information provided in this link and have constructed ...
2
votes
1answer
34 views
Query with conditional JOIN running slow
I have a query the first part of which looks like this -
With CTE AS (
select DISTINCT
A.col1,
A.col2,
.
.
.
from tableA A
JOIN tableB B ON
(
...
-2
votes
1answer
29 views
Subquery and subselection for Count/Number doesnt work
for example, for company (3) and his partners, I need the number of events with payouts. But i get only the number for one partner, im my example i have
i have 3 tables (t_company, t_partner, t_event,...
0
votes
1answer
46 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
1answer
38 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
1answer
14 views
How to get desired result set?
I have the following two tables
customers
| customer_id | firstname | lastname | email |
|-------------|-----------|----------|----------------------|
| 1001 | John | Doe ...
2
votes
3answers
845 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
1answer
39 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
1answer
52 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
1answer
124 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
1answer
13 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
0answers
26 views
MYSQL Multiple table optimization for advanced search
I have a users table where there is the basic information of each user:
CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`account_type` varchar(10) NOT NULL,
`email` varchar(...
0
votes
1answer
35 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 ...
0
votes
1answer
33 views
nontrivial joining a relation with itself
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
1answer
35 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 |
+------...
1
vote
1answer
65 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
2answers
78 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
1answer
31 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
0answers
30 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
2answers
444 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
2answers
61 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 ...
0
votes
0answers
31 views
Organizing SQL joins for readability
I have taken to writing my queries, especially those with lots of tables joined together, like so:
select <stuff>
from A
join B on B.pk=A.fk_B
join C on C.pk=A.fk_C
left join D ...
2
votes
1answer
50 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
1answer
52 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
1answer
45 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 ...
5
votes
1answer
91 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
1answer
31 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
0answers
29 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
1answer
64 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
0answers
16 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
1answer
36 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
...
10
votes
0answers
95 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 ...
1
vote
2answers
35 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
1answer
15 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
1answer
47 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
...
-2
votes
1answer
54 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
1answer
68 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
2answers
47 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
2answers
27 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
1answer
72 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 ...
