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
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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