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

learn more… | top users | synonyms

0
votes
0answers
9 views

How can I simplify this query ? ( 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 | ...
1
vote
1answer
30 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
0answers
8 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 ...
2
votes
1answer
78 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
21 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
2answers
81 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 ...
0
votes
2answers
167 views

Selecting Only One Record with Most Recent Date Using Table Join

I have a list of 463 CARRIER_CD. I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD. I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the ...
1
vote
2answers
63 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
71 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 ...
0
votes
0answers
64 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, ...
1
vote
2answers
649 views

Mysql improve Self join performance large table?

I have a task to help identify conflicting IP ranges , I want to highlight when one companies IP range falls within another's ... below is the sample table, I have converted the IP ranges from .dot ...
0
votes
0answers
29 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, ...
4
votes
1answer
174 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, ...
0
votes
1answer
31 views

How to get average of views before clicking on advertisement?

I've got two tables containing information about viewing and clicking on advertisement by each user: Clicks: userID Action Time 123 c 2016-01-08 01:57:00 123 c 2016-01-11 ...
2
votes
2answers
291 views

MYSQL joining 3 tables with left join

I have these tables: pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) ...
3
votes
3answers
44 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 ...
1
vote
1answer
126 views

placing conditions in a join query join on vs where

Is there and advantage to one of the following options over the other or are they the same ? select * from T1 Join T2 on T2.id=T1.t2_id and T2.field=value; vs. select * from T1 Join T2 ...
0
votes
2answers
13 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 ...
1
vote
2answers
113 views

Select highest id of each type in mysql

I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ...
0
votes
1answer
16 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 ...
0
votes
1answer
123 views

MySQL very long-running queries

I have a quite large db (800+Mb dump file) that I imported to my local server. It's a Wordpress db from witch I need to extract certain posts. There are around 160,000 posts inside. Currently I'm ...
1
vote
2answers
38 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
77 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
91 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 ...
1
vote
2answers
52 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
40 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
39 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
45 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
327 views

Data Analysis with MongoDB or MySQL

I have a need for processing some data in order to come up with some statistic. This is the scenario: Each user can answer several questions from the question list. They pick their answers from a ...
0
votes
1answer
66 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, ...
0
votes
1answer
29 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, ...
2
votes
3answers
110 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: ...
1
vote
1answer
25 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 ...
1
vote
1answer
29 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 ...
0
votes
2answers
106 views

MySQL get values from the other table by using inner joins

In mysql I have 3 tables called as ps_product_lang ps_order_detail and ps_image here are the tables sample data === ps_product_lang === CREATE TABLE IF NOT EXISTS `ps_product_lang` ( ...
0
votes
2answers
270 views

Remove duplicates from multiple joins

I'm using WordPress $wpdb->get_results for this query (it is simplified for question purposes): SELECT customer.id, customer.name, address.address appt.appointment_date, pay.date FROM customers ...
-1
votes
1answer
29 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
35 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 ...
2
votes
1answer
703 views

How to improve the performance of SELECT queries with multiple JOINs on a 50GB database hosted on AWS m3.medium MySQL RDS?

Example query: SELECT p.ListAgentEmail, p.ListAgentFirstName, p.ListAgentLastName, p.ListingKey FROM Property_RES AS p INNER JOIN Property_RES_COUNTIES_OR_REGIONS ON ...
4
votes
2answers
380 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 ...
-4
votes
1answer
24 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 ...
0
votes
1answer
25 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 ...
1
vote
1answer
55 views

Same query plan, different data set, very different query duration SQL Server 2012

This may be a terrible question, because I'm not sure how information I can include to help. We have data segregated by customer. One customer apparently has higher volume of data. The same query ran ...
1
vote
0answers
28 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 ...
0
votes
1answer
37 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 ------------------------ -------- ...
1
vote
3answers
66 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 ...
1
vote
1answer
581 views

Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around ...
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: ...