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

learn more… | top users | synonyms

0
votes
2answers
38 views

How do I write this cross Join in MySQL?

It should create a set of columns (ControlRange, SensorBias, SensorError, VDCurve, INSHalfCurve, INSSensCurve, ExogDEXCurve, InitGlucose) and all permutations of their values. I want the order given ...
3
votes
1answer
167 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
76 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
70 views

Join on multiple columns with OR

I have a table of 1 million record to join another table with 100000 records. However, there are 5 potential keys (lets assume Account Number, email address, membership number, alternative email, and ...
1
vote
1answer
38 views

UNION or SELF JOIN?

I have a table to hold parent and child posts together. +------+----------------+--------------+-------------+------------+ | p_id | parent_post_id | child_status | post_status | post_title | ...
1
vote
1answer
2k views

Joining multiple tables results in duplicate rows

I am running SQL 2005. I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. Here is my query: There are multiple tables that have ...
0
votes
3answers
71 views

MySQL optimizer won't use my index

With the following query: SELECT * FROM geoname LEFT JOIN alternatename ON geoname.geonameid = alternatename.geonameid AND alternatename.isPreferredName = 1 AND ...
0
votes
0answers
22 views

How to use COUNT() properly with a 3 table join

I have a database where I have driver details, their assigned routes and their stop locations. For each driver on the database I need to find the location (or locations, as it could be more than one) ...
0
votes
1answer
38 views

Join query with closest date in “ON” clause

I need to make an OUTER JOIN on two tables, based on 2 columns, first id an INTEGER id, which is pretty straightforward to use in the query, but the second one is a DATETIME, which doesn't actually ...
1
vote
2answers
1k 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 ...
0
votes
0answers
11 views

Schema of equijoin in relational algebra

Why does equijoin return a schema consisting of all attributes from both relations (including intersection) when equality between them have already been established? That is, we know that for any ...
0
votes
1answer
22 views

Cumulative data and dates - my joins aren't working

So I want to get cumulative data, for every day since a start date until an end date (or today). I have constructed a query that will get the data from a table, but for some reason, some dates are ...
2
votes
1answer
49 views

Join query with different column types in “on” part

In need to make a JOIN on 2 tables based on columns which have different column types. On table A, I have a DATETIME field and, on table B, I have a DATE and a TIME field, which combined would match ...
0
votes
2answers
62 views

WHERE on SQL with JOIN on large tables

We have a set of large tables (millions of records) on a MySQL DB, having schema like so (simplified): T1: id, uid, rid, text1, text2, int1, int2, ... T2: id, rid, tag_id, created_at T3: id, ...
3
votes
1answer
1k 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 200 101 300 I Need a result ...
1
vote
1answer
30 views

Efficiently perform multiple similar updates

I'm trying to extend some existing SQL (SQL Server) that builds a table of all items to act on using a number of queries. Some of these are very similar and costly, and I was hoping to get some advice ...
2
votes
1answer
204 views

SELECT values with a condition and JOIN

I am not sure that is a "SELECT/JOIN" problem but I really don't know how to formulate the question. Perhaps you could suggest a better title I have a table (table-a) with a list of items, I Want to ...
1
vote
1answer
49 views

How can I select records which are between 2 dates based on a date in a field in PostgresSQL?

I have two tables: 1 with an Unique ID with the first time they visited (in date format) and a historical addresses table with the same Unique ID, a start field (date field), an end field (date ...
0
votes
0answers
38 views

Mysql FULLTEXT index not used in Many to Many table join search

I've got a set of data in the Books table, with BookID as the primary key. I've also got Subject and Location tables, each of which is joined to the primary Books table with a Many to Many table. ...
0
votes
1answer
53 views

SQL Query help: co-workers and employees from one query

I have two tables defining users and user employments. The user table looks like this: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) COLLATE utf8_unicode_ci ...
0
votes
1answer
49 views

Mysql join non-unique columns

I have two tables, want to join them by an non unique column. CREATE TABLE `order_item` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `soi` char(1) DEFAULT NULL, `product` char(4) DEFAULT ...
0
votes
0answers
22 views

Is it possible to stack similar columns from a subquery and join the other unique columns in one query?

I am fairly new to the sql world and have been struggling with a query. I have two subqueries that are generating tables with two columns. The first table is part # and model_countH. The second table ...
0
votes
0answers
29 views

Is it better to filter a small set of row with a BOOL or through a JOIN?

Let's say that I have a simple Books table: Books ----- id name description ... It's a pretty small table, at the moment with 60 records and it can grows, but slowly. On a webpage I want to show ...
1
vote
1answer
18 views

DB2 LUW: how to influence query planner's choice of join

Suppose I have two tables, A and B. They "share" the same primary key, which is indexed in both tables. Table B also has a timestamp column which is separately indexed. I do a subselect on B using ...
2
votes
3answers
39k views

SQL Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update ...
0
votes
1answer
45 views

How to make an union on 2 joined tables without affecting the sum on the column? [closed]

I have the following tables: cant: id CANT_VAL COD_VAL fk_id_chest c1 c2 18059 18.56 R12 1 100 11 18060 11 R10 2 200 22 18061 15 ...
0
votes
1answer
39 views

Using group by on several tables with problems [closed]

I have a poorly-designed table in an Oracle database like this: sr: id an_rap 21 2015 rap: id cf 28185 123-123 ch: id fk_id_rap FK_ID_SR 12348 28185 21 dma: ch_id ...
0
votes
1answer
23 views

How can I group two joined tables

How can I group (by) two joined tables (in an Oracle database) like this: cant: id CANT_VAL COD_VAL fk_id_chest 18059 18.56 R12 1 18060 11 R10 2 18061 15 ...
0
votes
1answer
30 views

How can I group (by) two joined tables [closed]

How can I group (by) two joined tables (in an Oracle database) like this: cant: id CANT_VAL COD_VAL 18059 18.56 R12 18060 11 R10 18061 15 R11 col: FK_ID_COL_DMA ...
4
votes
2answers
58 views

Join subquery by year and month

I want to collect data from differed tables grouped by year and month. I'm using subquery for each sub data. Using mysql. SELECT YEAR(td.db_date) AS stat_year, job.total_jobs FROM ...
-1
votes
1answer
21 views

Trying to get all checkins open during day at specific times

Db structure looks somewhat like this. id, transdate, opentime, closetime 1 2015-11-02 2015-11-02 17:51:00 2015-11-02 18:51:17 2 2015-11-02 2015-11-02 17:58:00 2015-11-02 19:26:52 3 ...
-8
votes
1answer
8k views

Using inner join to get data from multiple tables [closed]

I have 5 tables: A (a_id, a1, a2) B (b_id, b1, b2, a_id) C (c_id, c1, c2, b_id) D (d_id, d1, b_id) E (e_id, e1, e2, d_id) Here is some information table b have a_id match with table a (a_id). ...
1
vote
0answers
20 views

Why does SemiJoin node receive so bad statistic even after runnning ANALYZE

PosgtreSQL 9.4 I have two tables: CREATE TABLE home_transactions( id serial NOT NULL, transaction_id integer ); CREATE TABLE transactions( id serial NOT NULL, user_id, amount numeric(13,4) NOT NULL ...
0
votes
1answer
213 views

SQL80001 - incorrect syntax near ')'

In the below code (that I've inherited), I'm getting the message "SQL80001 - incorrect syntax near ')'", the error being on the third line from the bottom with the + sign. I've looked at THIS SO ...
0
votes
1answer
50 views

Why does STRAIGHT_JOIN consume more CPU?

Why does STRAIGHT_JOIN consume more CPU than a regular JOIN? Do you have any idea? When I use STRAIGHT_JOIN on one of my queries, it speeds up the query like from 12 seconds to 3 seconds. However, ...
0
votes
0answers
29 views

limit aggregate function across join

I have a facts table, and each fact has one-or-more distributors in fact_distributors. A distributor is an entity, and for reporting purposes entities are grouped together into an entity_group via ...
0
votes
0answers
22 views

How can I combine join and random choice from one table but 2 conditions?

I want to choose 200 entries randomly from a table However, I have two conditions the first 100 should be white and the other black. How can I join them? One possibility to do is to run following ...
0
votes
1answer
61 views

Sum of values for each row

SELECT ratings.programme_id, programme.programme_name, ratings.ratings FROM programme INNER JOIN ratings ON ratings.programme_id = programme.programme_id I have two tables a and b a has id and name ...
0
votes
1answer
81 views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly ...
2
votes
3answers
196 views

Performance impact of NOT using a foreign key (many dedicated 1:many key-key tables vs non-fk generic key-key table)

We're using a lot of association tables to manage 1:many relationships among a variety of different objects in our system. To illustrate the question, two examples would be: users, events, ...
4
votes
1answer
55 views

Adding a second Group By column slows query down

I have the following query: SELECT 'TEXT', 'TYPE', 'DESTINATION', 'HEADLINE', 'COUNTRY_ID', 'COUNTRY_CODE' UNION ALL SELECT Data_1.TEXT, Data_1.TYPE, Data_2.Destination, Data_2.HEADLINE, ...
4
votes
0answers
154 views

Performance inner join vs left join involving parallelism

I have a query which takes about 8 hours to finish. Simplified, the query looks something like this: SELECT [Some attributes, inconsequential] FROM RBM.Financial_Events RFE INNER JOIN ...
1
vote
1answer
39 views

Syntax error in UNION ALL [closed]

I have syntax error in UNION ALL line 8.. what is the mistake? I'm using mariadb, (Innodb) SELECT 'Süre Bitiş Tarihi','Tc Kimlik No', 'Kullanıcı Soyadı, Adı','E-posta', 'Telefon','Diğer ...
2
votes
0answers
87 views

Materialized View - vs. New Calculated table for performance [closed]

I have a problem - I'm trying to de-normalize data tables in a data warehouse in preparation for use in the Business Intelligence/ Presentation layer. By denormalize I mean create a series of joins. ...
0
votes
1answer
69 views

Improve execution time of query in POSTGRES with multiple joins

I'm having the trouble with the following query due to the growing size of the database ( websites is around a million rows, misspelled goes up to a couple millions ) It can reach up to 165 seconds ...
0
votes
2answers
72 views

Improving performance on a join query

I have the following MySQL query that I am attempting to run against a set of tables prepopulated with data: $sql = sprintf(' SELECT video.id, video.name, video.created_at, video.duration, ...
0
votes
2answers
72 views

Efficient way to join two tables with one to many relationship

This is interview question asked to me in interview and i was not able to answer. Please help.. What is the most efficient way to join two table with one to many relationship between them
1
vote
1answer
53 views

Joining 3 Tables

I have 3 tables as per below: **ATTRIBUTE TABLE** PeopleAttrCODE PersonCODE AttributeCODE Value 1 1 End Date 20151231 2 1 Start Date ...
0
votes
1answer
32 views

Update rows in a table with an index column starting from 0 with consecutive numbers

I have a few tables in a Oracle db with some "rule": The INDEX_COLUMN must start from 0 and must be consecutive numbers and the column NRCRT must start from 1 and also consecutive numbers. I've made a ...
2
votes
1answer
54 views

How to match column names and values from joining and related tables?

I have the following DB structure: cars: id make features: id name cars_feature: id car_id feature_id value I want to be able to select all cars with all features and return results with ...