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

learn more… | top users | synonyms

0
votes
0answers
3 views

Footer with counter of records, using union of query

I have a report that has a header (display the current date), I use UNION to join the header with the main query that returns multiple records, it has 10 columns. After that another UNION for joining ...
23
votes
1answer
3k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
0
votes
2answers
27 views

How to select the largest value of the same column in multiple tables?

I have multiple tables which have some columns with the same meaning, eg., event_timestamp. The schemas are like following: Table A: ...
0
votes
0answers
14 views

Calculations involving thresholds between two tables

I have a table comment and a table price like below. The key columns in both tables are ticker_id, price_datetime and price_open. The last column threshold in table comment (which currently NULL) is ...
0
votes
0answers
21 views

Can I optimize a left join for a single match?

Consider a join A LEFT JOIN B ON A.id = B.a_id: B.a_id is nullable -- most but not all B records point to an A record. A can, at most, only have one matching B record (business logic). Not all A ...
0
votes
1answer
16 views

Mysql join not working

I have database with two tables: logs & src_info mysql> describe logs; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | ...
0
votes
1answer
7 views

Joining WordPress Comments with Post Titles from MySQL DB

I'm working with a wordpress database and need to get some data on the comments. The goal is to get comments from a decommissioned site. I have the database running on my local system and can ...
1
vote
1answer
25 views

Is it possible to pick next row's cell value on same column if condition not matched?

I have a table flaggedcomment and a table price. In flaggedcomment, there are two empty columns Price_DateTime and Price_Open which I want to copy from price, depending on the ON conditions. Query as ...
2
votes
2answers
47 views

Is there a way I can speed up queries against this particular view?

I have three tables in a PostgreSQL database that I'm querying via a view and some joins. CREATE TABLE network_info ( network CIDR NOT NULL, some_info TEXT NULL, ...
0
votes
2answers
29 views

join value in a RETURNING which is not inserted

Im doing an insert from a query which joins two tables, then I want the new ID from the inserted row, and a field from the original row which is not involved in the insert.Is it possible? I get a ...
0
votes
3answers
57 views

Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

I'm using Postgres, which enforces the constraint that all columns in a SELECT...GROUP BY must appear in the GROUP BY clause or be used in an aggregate function. Lets say I'm modelling peoples' cars, ...
1
vote
0answers
22 views

Grouping results with left and inner join with zero counts

I have a query like SELECT `campaign_question_options`.`text`, COUNT(`campaign_submission_answers`.`answer`) as `count` FROM `campaign_questions` INNER JOIN ...
0
votes
1answer
107 views

How can I optimize this Cross Table COUNT() mysql query?

I am trying to cut down the retrieval speed of this MySQL query. All instances of COUNT() point to the PRIMARY key of each field. Currently this query makes my MySQL hang. It seems ridiculous as I'm ...
1
vote
2answers
158 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) ...
0
votes
1answer
83 views

MySQL update one table from another joined many-to-many

I have two tables, events and plays joined many-to-many by a third table plays_in_events. Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in ...
0
votes
1answer
29 views

Inner join and group by [closed]

So I've got two tables. Accounts has three columns: id, Account_ID, Account_Name Tickets also has three columns: id, Account_ID, Description I can use the following query to get the most ...
0
votes
0answers
26 views

MySQL Query Causing Massive Slowdown Over Time

I've been experiencing massive slowdown on my server after launching a new website. Using MONyog I can see that after an apache/mysql restart, the queries/second starts out nice and low for around 10 ...
0
votes
1answer
63 views

how to convert inner join to where clause [closed]

I have a sql query to retrieve a data from a complex database. Following is the sql query, select * from A a inner join B b on a.a_id = b.a_id inner join C c on c.b_id = b.b_.id inner ...
1
vote
1answer
54 views

Join conditions and Where conditions in Left Join

STUDENT table contains a list of students in my school Database LIBRARY_BOOK table contains the information of each Book that is with currently with any student. each student is allowed only 1 book ...
2
votes
1answer
117 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
1
vote
1answer
212 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 ...
0
votes
1answer
261 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
2answers
133 views

SQL join with multiple columns as FK to same list table

I have a table that has 5 references to a list table for normalization of the first table. So in the table 1 a row looks line other columns..., reference_1, reference_2, reference_3, reference_4, ...
0
votes
1answer
119 views

SQL SELECT - adding field to SELECT with JOIN is changing the results

I have a SQL sentence with JOIN, running on mysql 5.5.16: SELECT DISTINCT (ot.orders_id), ot.value FROM orders_status_history osh INNER JOIN orders_total ot ON ( osh.orders_id = ot.orders_id ) WHERE ...
0
votes
2answers
36 views

What indexes should one use to optimize a PostgreSQL query with a JOIN depth of 2?

Disclaimer: I am relatively new to PostgreSQL. I'm wondering how to optimize a query that does 2 INNER JOINs. My scenario is fairly simple: Select Posts with a photo (Posts.photo IS NOT NULL) and a ...
0
votes
1answer
200 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
37 views

same table on different commands (inner join)

Testing some commands of MySQL, I've come across these two comands resulting on the same table. Command 1: select sales.relatory_code, sales.date, destiny.exportation_name from sales, destiny where ...
0
votes
0answers
15 views

Local Server Slow When Joining

I have two cira 600MB tables that I am querying as such: SELECT id, name, url FROM names, urls WHERE metaphone = 'ALX;FLX' AND urlId = urlRef; This runs fine on my live server ( ~ 0.02 sec,) but on ...
0
votes
1answer
48 views

select values from a table where column values should be different [closed]

Let us say there are 4 columns in a table . cpt,cpt4mod,itemcharge,eff_date. condition1: if the values of cpt,cpt4 and itemcharge are same, but eff_date is different. Then I need to fetch the ...
1
vote
2answers
244 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 ...
1
vote
2answers
75 views

Understanding multiple table join with aggregation

I have a basic question on how JOIN works on multiple tables. I want to count occurrences of Foreign Key in link1 & link2 CREATE TABLE main ( id SERIAL PRIMARY KEY, name text NOT NULL ); ...
1
vote
1answer
147 views

Joining on a field with different values

I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually ...
2
votes
0answers
48 views

SQL aggregate function performance in a table with many records that is well-indexed

My question is about 3 tables that stores data of a single entity, and there is a report generation feature that refers these tables and calculate COUNT and AVG information. There are three tables: ...
0
votes
1answer
20 views

Compare two columns for difference mark the difference in mysql

I need to compare two fields and mark the similar fields in third table. For Example: Tables customer: phone id, phonenumber followstatus received_Customer: rc_id, rc_phonenumber. I ...
1
vote
1answer
114 views

MYSQL query to find records between two date range

I am working in a web application using PHP and MySQL. I have two tables Employee employee_id email_id 3 tl1@jasmine.com 4 agent1@jasmine.com 5 ...
2
votes
1answer
43 views

Sorting Table and get Position of row

I have a Table with 3 rows. Player, Time, Server. Now, I want 2 things and I don't know how. For Server there are 3 worths available. 1, 2 and 3. Server and Player are a Primary Key together. User ...
0
votes
1answer
56 views

Best practice - use of derived table or use the original table in join?

Imagine I have the following tables in Teradata : TableA : Field1|Field2|Field3 TableB : Field1|Field4|Field5 I am trying to join these two tables Method 1 : SEL TableA.Field3, TableB.Field4 ...
3
votes
2answers
861 views

Outer Apply vs Left Join Performance

I am Using SQL SERVER 2008 R2 I just came across APPLY in SQL and loved how it solves query problems for so many cases, Many of the tables I was using 2 left join to get the result, I was able to ...
1
vote
1answer
36 views

How to join fixed row table against mapping table and reveal unmapped records

I have two tables and I am having trouble figuring out how to join them to meet my projection needs. Here is a scenario that illustrates the problem 1. The first table has a fixed set of values/rows; ...
2
votes
1answer
95 views

SELECT query with DISTINCT on a table-structure for graphs is very slow

I have 2 tables where nodes contains about 60m rows and edges about 500m rows (both growing fast). These two tables represent a directed graph. CREATE TABLE edges ( node_from bigint NOT NULL, ...
2
votes
2answers
33 views

Get all (non repeated) distribuidores with their paises given a producto_solicitud

I have this schema: And as title says I need to get all records from fabricante_distribuidor by given producto_solicitud.id value. I'm doing in this way: SELECT ...
5
votes
1answer
71 views

Sort by number of related rows in referencing table

Let there be two tables: Users id [pk] | name --------+--------- 1 | Alice 2 | Bob 3 | Charlie 4 | Dan Emails id | user_id | email ----+---------+------- 1 | 1 ...
1
vote
1answer
44 views

How to join a table with a table valued function?

I have a user defined function: create function ut_FooFunc(@fooID bigint, @anotherParam tinyint) returns @tbl Table (Field1 int, Field2 varchar(100)) as begin -- blah blah end Now I want to join ...
0
votes
1answer
26 views

SQL query to aggregate data from three tables

I have the following three tables: The "users" table: user_id first_name last_name username password The "users_addresses" table: address_id user_id street country_id email The "countries" ...
1
vote
1answer
22 views

MySQL join for two queries

I have the following two queries... mysql> select id, title from events; +----+-----------------------------------------------------------------------------------------------+ | id | title ...
3
votes
1answer
429 views

Sql server table insert performance optimization

Setting In a datawarehouse, I am joining a fact table to 20 dimensions. The fact table has 32 million rows and 30 columns. This is a temporary staging table so I don't have to deal with other users ...
2
votes
2answers
128 views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES('{"parent":null,"children":[2,3]}'); INSERT INTO test(data) ...
0
votes
0answers
30 views

MySQL Multi Count and Filter

Table: Event ============== ID OldUserID NewUserID Date Table: User ============== ID Forename Lastname .... How can i filter by date column like so... this code is pseudo... SELECT ...
0
votes
1answer
2k views

How to create the multiple sum query with joins

Please consider following database: Table `invoices`: -- invoice_id Table `amounts`: -- amount_id invoice_id type_id amount I am trying to get the sum for each type from table like this: SELECT ...
0
votes
1answer
27 views

optimize my query mysql

I have the following query: SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId WHERE d.castName ...