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

learn more… | top users | synonyms

3
votes
2answers
210 views

Adding 4th table to join jumps processing time from seconds to over 20 minutes

I am trying to join a total of 4 tables together. When I only join 3 of the tables together I get a result set of about 220,000 records in about 2 seconds, but as soon as I throw the 4th table in, the ...
0
votes
1answer
38 views

Single value from a list of comma separated values when used in inner join in SQL Server

Can anyone please help me with a query to return a single value from a list of comma separated values when used in inner join. I can paste the example query here. select * from Test.dbo.Test1 ...
-1
votes
1answer
38 views

Join between a table and a recursive table

I've that two table, I know they are not well thinked but my client did this tables and I cannot change them. TABLE A id | codigoFatherTABLEB | codigoSonTABLEB TABLE B id | name | codigo | idPadre ...
0
votes
0answers
41 views

Selected Child Categories based on parent categories from another table

I have two tables rules With three step hierarchy: |id | name | parent | |---|-------|--------| | 1 | A | 0 | | 2 | B | 0 | | 3 | A(1) | 1 | | 4 | A(2) ...
0
votes
1answer
43 views

MySql - Get all children in JOIN if one child matches a condition

I have a one-to-many relationship between two tables Parent and Child. I want to get all children if one child matches a condition. Something like below: SELECT * FROM Parent p INNER JOIN Child c ON ...
0
votes
1answer
38 views

Speed up SUM() on a result with JOINS in MySQL

I have a query with a combination of 6 left joins and inner joins. The query gives a result of 25000 records in 112ms. When I do a SUM() the query takes 4s. A SUM() another table with 35000 records ...
2
votes
1answer
28 views

Postgres LEFT JOIN with recurring dates and users

I'm trying to get a query to show me if users have a record on a particular day, and if they don't it would return a NULL. What I have so far is a I create a sequence of days, then LEFT JOIN it, if I ...
3
votes
0answers
51 views

Do Large Hash Joins Contribute to Stolen Pages?

On a SQL Server 2014 SP1 Enterprise Instance, I'm performing an outer join between two partitioned tables on a numeric(16,0) + char(1) composite key. 100 GB is the max server memory setting on a 128 ...
1
vote
0answers
58 views

Simplify Month Over Month % Change, 4 Months Moving Average %

invoice created_at | amount | status | ------------+--------+--------- 2016-06-01 | 50 | 2 | 2016-05-30 | 150 | 2 | 2016-04-29 | 32 | 2 | 2016-03-28 | ...
2
votes
0answers
15 views

How does the use of derived tables speed up sorting?

In the article 45 Ways To Speed Up Firebird Database, it states that Use derived tables to optimize SELECT with ORDER BY/GROUP BY Another way to optimize SQL query with sorting is to ...
1
vote
1answer
35 views

Decomposition and lossless joins

If someone walk me though the following questions I'd be very grateful. Consider a relation R(A, B, C, D, E) with the following functional dependencies: A, B → C D, E → C B → D i) ...
2
votes
1answer
93 views

Estimate result size of Natural Join

I have the following tables R1(A, B, C) with 1.000 rows R2(C, D, E) with 1.500 rows R3(E, F) with 750 rows where a bold letter denotes a primary key. I need to estimate the number of rows of the ...
0
votes
2answers
22 views

Unique pair checking query improving in pivot table

First of all, I have a solution, but I do not know that query can be transformed to uses join or to more effective query. I just want to improve my skill. I have seen an example online. So I have a ...
0
votes
1answer
90 views

What is difference between JOIN and simple query

I have two tables in database one is Orders and another one is customers Orders Table Contains : OrderID , CustomerID and OrderDate Customers Table Contains: CustomerID , CustomerName , ContactName ...
1
vote
2answers
81 views

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | -----------...
0
votes
1answer
74 views

Delete query does not end

I have a following table with 1 million records : CREATE TABLE `activity` ( `id` int(9) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `subscription_id` int(10) unsigned NOT NULL, `...
0
votes
0answers
34 views

Updating rows in multiple different tables using JOIN

Following is my MySQL SELECT query and it works. SELECT candidates.CandidateName, candidates.CandidateVotes, candidates.Party, candidates.win, candidates.NameOrder, ...
3
votes
3answers
49 views

Query to find missing status events

I'm hitting a road block as the best way to query a related table. I have two tables: main and status_history. Right now, I can identify records from main with no status_history at all, but I need to ...
0
votes
2answers
14 views

MS sql query value one table but not another join table where*

I have [tbl_AllStudentsEmail] where I get my students distribution list. I also have [tbl_CourseID] where some students are registered (not all). Students record are registered here along with the ...
0
votes
1answer
36 views

Access Web App query 3 Tables with aggregatios - Need a workaround

I'm designing an Access 2016 Web App for inventoring our computers. The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC. The computer-value in RAM ...
1
vote
2answers
40 views

Does the EXIST clause only return distinct rows by its nature?

I have two queries that appear to be logically equivalent, yet they return different result sets. I am using the AdventureWorks2012 database. The first query uses an EXISTS clause with a sub query. ...
2
votes
3answers
92 views

Join between different frequency tables

Assume that I have two tables, DailyTable and QuarterlyTable, where each have 3 columns: ID, Date, and Value. As the names indicate, the frequency of data stored in DailyTable is daily, while the ...
0
votes
3answers
125 views

How can I pivot a view into a join so I get multiple query rows as columns in a single row? [duplicate]

I have a view into which I am pulling properties of vehicles, such as tire size, engine capacity, color, number of doors, etc. This data is stored in multiple tables but I have simplified the data ...
0
votes
1answer
101 views

SQL query to count number of payments made in a time period with data from two different table

Could someone please tell me how to write sql query for the following question? I am not able to get how to write the condition where it selects those user_id which is present on the said date and ...
1
vote
2answers
61 views

How could I get the value as Y or N in one table if value is exists on another that meets certain condition?

I have to generate simple report from three tables named customer, product, and order. My report should have a column that shows whether the customer ordered that product or not. How could I get the Y ...
4
votes
1answer
49 views

PostgreSQL join subquery can't restrict query

I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances. I'm trying to run a query ...
0
votes
0answers
47 views

How to give a prefix to all columns of a JOINed table in MySQL?

I have a data schema like this: car id type -> ENUM('Tesla', 'AstonMartin', 'Bmw') common_attribute car_tesla car_id specific_attribute_a specific_attribute_b ...
2
votes
1answer
53 views

How to JOIN a table with a name that equals to a determined value in MySQL?

The model of the (PHP & MySQL based) Zend Framework 2 application I’m currently working on contains a inheritance similar to the one described here. To map this to the database I’m using the ...
0
votes
1answer
32 views

ORA-00932 on subquery join from subquery join

The only guess I have is that perhaps the size of the subquery is considered a CLOB? I have no null values for the fields selected AFAIK. SELECT DISTINCT kbq_ent.qid AS kbq_ent_qid, kbq_ent....
0
votes
1answer
69 views

comparing left join and outer apply doing the same thing

I have an update trigger on a table that that insert rows in another database/table the query look like insert into otherdb.table select manyfield from inserted left join (select id, ...
1
vote
1answer
26 views

Query: How to join on different tables depending on the filled column?

I'm pretty new to the field of SQL, but already solved a lot of problems, so until now i am familiar with the different possibilites of JOIN(s) (explicit and implicit syntax), group by, distinct and ...
2
votes
3answers
113 views

Poor performance of JOIN, WHERE, ORDER BY combination (MySQL)

Goal: select 10 most recent rows from posts which are related to a specific user. There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows). posts columns: --------------------------...
4
votes
1answer
214 views

Select multiple columns from subquery - Connect by

I have a table which manages folder structure. Hence it has a parent-child relationship. We trigger a query to sum all documents in the complete heirarchy: SELECT folder_name name, ...
1
vote
1answer
31 views

Using a join operation with conditionals

I have two tables that I'm working with in my database. I am using SQL Server 2014. One is 'versions', that contains version_id, version_number, start_date, end_date and server_id. The other is '...
0
votes
1answer
54 views

MySQL Query fix and speed up

I'm newish to MySQL and a lot of my query is pieced together from this site and trial and error, so any help is appreciated. If I left out anything, or more detail is needed, I will be happy to give ...
-1
votes
1answer
33 views

Multiple references to another table, but I don't know how many

I have two tables in a database, folowwing this model: scientists : id (int) | name (varchar) | surname (varchar) | publications (int) publications : id (int) | fulltext (varchar) I would like to ...
2
votes
0answers
38 views

How to avoid row locks on both tables joined in an update?

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
4
votes
2answers
392 views

Outer self join filters vs subqueries

I have a table storing information about tickets. There is one record when ticket is sold and another when ticket is used. There is a column called TransType which is set to either 'sold' or 'used' ...
2
votes
1answer
51 views

Can I avoid a join for my select procedures?

Here is a really simple example of a select procedure I'd like to create, although it's only partly done (it's pretty self-explanatory): delimiter // CREATE PROCEDURE mysql.selectTables (_first_name ...
0
votes
1answer
28 views

How to return null values from a joined table if the matching row is missing?

Here is what I need to achieve: person_id last_name first_name region_id region name 1 barnum phineas 1 maricopa 2 loman ...
-4
votes
1answer
25 views

Query to sort through history table

I have the above tables and want a query that will give the results as above. Basically I have a table of history where I want to display single row by adding two column that show prior Group from ...
1
vote
0answers
31 views

Groupwise max then join

I have a votes table that has many subitems. I have a subitems action table that records what action was performed by which user on which group, on which subitem, on which vote Users can either cast/...
1
vote
3answers
68 views

How should INNER JOIN query look?

I am trying to fetch some records using INNER JOIN. The problem is that I am not sure what's the best (recommended) way of doing it. What "pros" and "cons" have the queries shown below? All of them ...
0
votes
1answer
39 views

Help on joining 3 tables Oracle XE 11g

I am trying to return the first name, surname, subject and mark from the following tables: SQL> desc students; Name Null? Type ------------------------ -------- -------------...
0
votes
0answers
11 views

Find missing associations in many to many relationship [duplicate]

I have client and category tables with a many to many relationship via client_category_association. table: client id name 1 ek 2 do table: category id name 1 un 2 dos 3 tres table: ...
1
vote
1answer
33 views

Join second table based on third table with conditions

I"m using Doctrine for a website that has 3 tables to connect together. In simplest terms, they look like this Contractor - id - name ContractorJob - id - contractor_id - job_id - status Job - id - ...
0
votes
2answers
53 views

need help joining two tables with a having count clause

I am a network guy so I apologize for this.... I have two tables I need to join and pull names out of. pt_basic has the patient names and codes I need, ptc_diagnosis has their diagnosis. I need to ...
3
votes
1answer
104 views

DELETE rows which are not referenced in other table

I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group. I want to delete all rows from link_group where no related row in ...
3
votes
1answer
25 views

How does Hash Join works with relational operators other than equality operator?

I have been reading about Hash Join and how does it work in physical level. However, there is some thing that I don't understand (because of my lack of knowledge). This is the algorithm of Hash Join ...
1
vote
1answer
48 views

Galera MariaDB not joining with IST

Until now, I have a single MariaDB 10.1 node running as Galera Cluster. So, now I want to join the next one. systemctl start mysql initiates the snapshot state transfer (which takes about 10 hours ...