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

learn more… | top users | synonyms

1
vote
1answer
157 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 ...
-4
votes
1answer
45 views

Using inner join to get data from 5 tables [on hold]

I have 5 tables: A (a_id, a1) B (b_id, b1) C (a_id, b_id) D (d_id, d1) E (a_id, d_id) Table C has a_id and b_id match with table A(a_id) and B(b_id). Table E has a_id and d_id match with table ...
1
vote
1answer
22 views

Need help rectifying IN/NOT IN clause

Hi there everyone! I have built a link/content sharing site. The way it works is that each link can have multiple categories assigned to it. A video of a motorcycle race might have motorcycle, ...
0
votes
1answer
31 views

MySQL: Get comma separated values from a table, should not exist in another table [on hold]

I have two tables, table structures as below. Table: items Id item_name item_ids 1 pens 10,11,12,13 2 books 20, 30 3 computer 50, 60, 80 Another table : ...
0
votes
1answer
130 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 ...
1
vote
1answer
80 views

How to put the results of a query into a variable?

I have written a Postgres anonymous block. In which I have written join query on multiple tables like: select a.portal.*, a.p_fb_config.*, a.p_gplus_config from a.portal left outer join ...
1
vote
2answers
169 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
2answers
38 views

Join multiple Tables by date

i got some problems on my approach to join a couple of tables by a date range. This are my tables: RETURNS: RETURN_DATE RETURN_AMOUNT 02.05.14 2 02.05.14 3 02.05.14 1 ...
0
votes
0answers
11 views

Postgres Join that allows for an empty table [migrated]

I have the following schema, and I'm having a lot of trouble joining these tables properly. I'm trying to get all folders that don't have a file updated in the last 10 mins in them. But I want to ...
1
vote
1answer
258 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
13 views

Adding a column to aggregated data where new value only applies to a subset of the aggregation

Having trouble explaining this. I have a table like this: Label | Value A | 3 B | 1 C | 4 And another like this: Label | Value | ID A | 1 | ID1 A | 1 | ID2 C ...
0
votes
1answer
282 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 ...
2
votes
2answers
40 views

Is it a better idea (performance is key) to store in a normalized table and retrieve by JOIN or in a single nvarchar column and retrieve using LIKE?

NOTE: The application is not really about websites or keywords, I am using that here only to provide reference points for discussion. I have a data set that is really just a string of words. Think of ...
0
votes
1answer
124 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
89 views

Query to return zero if nothing exist

I have a table With currency, paymenttype and invoiceamount. I have to write a query to get currency, paymenttype and total invoiceamount made. This is pretty simple with group by. But actually I ...
0
votes
1answer
21 views

How to split a multivalued column, do a lookup and update another column?

This is a part of my previous question. How can I achieve the following for all the records in my table using a single query? SET @TeamMembers = TeamMembers from tblProjects where ID = '125' ...
0
votes
0answers
19 views

How to get list of ids from joining two tables (one to many) whose results is not present in a list?

Say Table 1: tID, tName Table 3: t2ID, tID, tSubjects One tID can be have multiple t2ID. What I want is list of tIDs who's t2IDs should not just have a list of t2IDs (1,5,6). i.e They can 1,2,5,6 ...
1
vote
0answers
54 views

Limit relation one to many or one to one

In a relationship one to many or one to one you can limit the relationship of the related table? For example, I have a table 'cliente' and another 'acct', each 'cliente' can have thousands (perhaps ...
0
votes
1answer
43 views

Mysql JOIN with 25million rows

Recently I got stucked with a table where its grown to +25million and I have got a query that's doing a JOIN on it. The query take 2.5hours to execute even though I got Good 96GB RAM and RAID10 with ...
0
votes
2answers
37 views

Join that returns always 1 record

I have a table that holds tests. For every test you have a generic one (TYPE field is NULL) but for some tests you can have also more specific tests (TYPE field has a value). I need a join to this ...
-1
votes
1answer
29 views

Mysql query giving incorrect values [closed]

When this SQL query is executed it gives incorrect value, please advise select sum(cash_management.cash_in), sum(cash_management.cash_out), sum(cash_management.pay_in), ...
1
vote
2answers
683 views

LIMIT to one row per ID

This query gives me multiple hits because of each assetid may contain many vb.title. How can I limit the result to only 1 hit for each assetid? 'select DISTINCT v.id, v.purchasedate, ...
2
votes
1answer
124 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 ...
0
votes
1answer
139 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 ...
-3
votes
1answer
14 views

Outer join with blank spaces to accomodate both sides

I am trying to join two tables both relating to cities. One table has the columns City and Writer, the other has City and Park. I want to end up with a table that has the columns Writer City Park. If ...
2
votes
3answers
75 views

Find duplicate records across one-to-many relationship tables

Let's say I have two tables that have a one-to-many relationship: People and Attributes. I'm trying to find duplicates based on the first_name, last_name, and all attributes must match exactly. ...
0
votes
2answers
157 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, ...
2
votes
1answer
76 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
2answers
50 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
2answers
62 views

Creating joined rows from multiple tables on date field

I need to be able to take Table A and find the records that match in Table B and Table C based on its ID and add them together. The trick is that I only want to add the results from Table B and Table ...
0
votes
1answer
204 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 ...
2
votes
1answer
31 views

INSERT multiple values via join

I have three tables, representing two entities and a connection relationship. Each of the entity relations has an artificial surrogate key used for foreign key constraints in place of natural keys. ...
2
votes
0answers
18 views

Query optimization - contact should be extracted at most once for a particular customer

I have created a database for a telemarketing company Scenario is, the telemarketing company is selling contacts to the customers. Customers log on to the website and search for the contacts for ...
-1
votes
1answer
28 views

Joining 3 tables of given sizes in most efficient way

Say I have 3 tables, A, B , C of sizes 10, 10^4 and 10^6. What is the most efficient way to join them? This is my reasoning. Assuming you join over primary key, if you join A with C first, you will ...
1
vote
2answers
278 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
1answer
429 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
38 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 ...
-3
votes
0answers
28 views

SQL pick 3 max values from 2 tables during the specific time period

I'm new here... Could you please help with this, probably simple, sql query. I need to: list 3 max values from two linked tables; Table A: (NAME, ID) Tom Clancy 123456 Homer Simpson 987654 Lisa ...
1
vote
2answers
66 views

1 hour query, is there another way to do this?

I saw a couple questions similar to this but none could answer my question, really. I have two tables, and I am trying to find those rows where a match is found. user has about 350,000 entries and ...
0
votes
0answers
33 views

count rows with same value and get column from other table

I'm trying to count product numbers for a period of time there are three tables. invoice (table) Column Column ------------------------------------------- |invoicenumber ...
0
votes
1answer
22 views

How to use a group by in inner join

I have Two tables Table 1 ID totalamount 100 1000 101 500 Table 2 ID Individualamount 100 500 100 300 100 100 101 ...
0
votes
2answers
85 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 ...
1
vote
2answers
61 views

Calculating percentage from two tables

I have two tables: First one contains unique mentees under one mentor, so mentors can have more than one mentee in that table. Second one contains interactions of mentors with mentees on different ...
0
votes
1answer
44 views

What's the point of SQL joins if you have OLAP?

I want to understand the purpose of using SQL joins from a business analysis perspective. Let's say I want to analyze customer data. Why would I want to pull data from multiple tables using join ...
0
votes
0answers
15 views

Use join to aggregate data from various normalized tables

I have a database as follows: tbl_clients (Primary Key: client_id, Foreign Keys:place_id,district_id,zone_id,country_id) tbl_places (Primary Key: place_id) tbl_districts (Primary key: district_id) ...
1
vote
1answer
111 views

How to optimize a very slow query with joins and group by?

I have this GTFS database : All the ***_id columns are indexed, and there are foreign keys between tables (such as a FK on trips.route_id to routes.route_id). So basically the link between a stop ...
0
votes
2answers
25 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
22 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
22 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 ...
24
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 * ...