Questions tagged [join]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
124 views

Complex queries in CouchDB across multiple documents

I'm relatively new to CouchDB (more specifically Cloudant if it matters) and I'm having a hard time wrapping my head around something. Assume the following (simplified) document examples: { "docType"...
0
votes
2answers
136 views

How to sort rows after union

I have two tables that I want to combine. I need to be able to show all DelDates that had changes from the original (record 1). Record 1 as the first Record and Record 0 (table 2) always being the ...
0
votes
1answer
107 views

Postgres optimizing join of 2 large tables

I have 2 tables one with around 1M entries and other around 4M. I want to join these tables in a materialized view. But the materialized view creation is taking too long ~4 hours. I don't see the ...
1
vote
1answer
5k views

full outer join not working

I Want to do a full outer join on 2 tables but I get this error, what am I doing wrong? mysql> select * from cajas full outer join almacenes on almacenes.codigo = cajas.almacen; ERROR 1064 (...
0
votes
1answer
78 views

Joining a column from another table with InnoDB engine

I'm using MySQL with InnoDB and I want to join the location column from products table into a productStock table. I have this table 'products' mysql> select * from products; +-----------+----------...
1
vote
1answer
50 views

MySQL SELECT slow, but only 2 x 300K rows and indexes

Have the MySQL SELECT query below that is awfully slow. It takes ~1.0 seconds to execute despite having only 300K rows and indexes, so I would love to find a way to get it to execute faster since it's ...
4
votes
3answers
514 views

Is a join faster with 5 tables joined together Or 5 separate queries? [duplicate]

I have confusion that I already have in my application 5 tables say a,b,c,d,e and have relationship with each other like a.b_id is primary key of b and chain goes on. Now my question is that which ...
2
votes
1answer
41 views

Caveats of OUTER JOIN on nested JSON value

I'm writing a query which is supposed to find the elements from a list which DO NOT exist in the DB. My first attempt at this was to use a nested query where the first query fetches the ids, then I ...
0
votes
0answers
12 views

What is the best relationship for a pair of tables that have optional dependencies but aren't really related?

I have two tables inspections and tasks. The inspection is allowed to have a single task but not many. Inspections don't need tasks and really there is no direct relationship. However, I have a link ...
0
votes
1answer
94 views

Counting and grouping over multiple OUTER JOINs

I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a ...
0
votes
1answer
19 views

Get missing weeks by country

I've got one table called "Units_Sold" which has these fields: year, month, week, country, units and another table called "Dates" which has these other fields: year, week, month ...
0
votes
1answer
245 views

MySQL multiple table joins and multiple data levels - best practice

I have multiple tables i wish to join up to produce one big query with specific columns summed. I was having a problem getting the correct summed columns in the final query (i believe this was ...
51
votes
1answer
56k views

Postgres Count with different condition on the same query

EDIT Postgres 9.3 I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2 The current query is working fine which looks like this: Basically it is a 3 table inner ...
2
votes
1answer
106 views

Join table into different columns

I have two tables (simplified). table1: - id (serial) table2: - table1_id (integer) - value (text) - type (integer) I want to join these two tables, but depending on the type, I want ...
4
votes
1answer
4k views

Better way to select from nested tables in PostgreSQL

I have the following schema with millions of rows in the match table and each match have two sides (away and home). I want to create a view which shows the most significant data regarding the matches ...
0
votes
1answer
90 views

JOIN type for MS-Access report query

I have 7 tables, 6 of which are in a one-to-one relationship with a table named SLD joined with SLD_ID. SLD has 17 records and I need them all displayed regardless of matches in other tables. Which ...
0
votes
1answer
19 views

Left Join table with first match historically

What I want to accomplish is to join TableB to TableA where TableA.date <= TableB.date, but only the the value with the nearest max date to Table.B.date. Currently, what I get is all the previous ...
0
votes
1answer
253 views

Drastic change in query speed when changing from Oracle 9i to ANSI outer join syntax

I am working on simplifying a query originally written for Oracle 9i. In the process I changed the join syntax from the Oracle 9i syntax to ANSI. The names of the tables and columns have been changed ...
0
votes
1answer
265 views

Poor performance on MySQL 5.7 on joined tables ( scanning wrong table ?)

I have a joined table (News, Publishers) with indexing. The query is working fine on MySQL 5.5. After I upgrading one of the server to MySQL 5.7, I start noticing high load, high CPU, and slow query. ...
0
votes
1answer
10 views

Selecting non-aggregate column on 3 table inner join

I have 3 tables in a standard parent/child kind of "belongs to" relationship in postgres: donations --(belong to)--> campaigns --(belong to)--> organisations Each table has an id ...
-1
votes
3answers
49 views

How to aggregate in the WHERE clause referencing the subquery from the FROM clause

I got the following query so far: SELECT q1.ID, Content, Volume, CustomerID, runtot FROM ( SELECT ID, Content, Volume, CustomerID, SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS ...
0
votes
1answer
284 views

How to access Multiple DB2 Relational Databases In A Single Query

I need a solution for below: There are Two DB2 instances with two authorization ID on the same server and can be access using same user User1. Example: To access Inst1DB, I use the following ...
0
votes
1answer
120 views

Slow query with multiple group by

I have a query to get all the comments from threads where a given user has commented and not yet seen the comment. So if user 1 commented to a topic, then user 2 commented after his comment, then user ...
79
votes
3answers
115k views

What is faster, one big query or many small queries?

I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, ...
0
votes
2answers
175 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` ( `...
1
vote
1answer
12 views

LIMIT on table with 1 to many

I have 3 tables (A, B and C) that connect 1:m:1. I need to JOIN the 3 tables and apply a limit not on the total number of records, but on the number of records used from table A. This means that ...
0
votes
1answer
185 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
1answer
268 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
1answer
273 views

Oracle DB union two select statements which return multiple rows

I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more than ...
1
vote
1answer
35 views

GROUP BY multiple criteria with difference in row fields within a group

Everyone, I am struggling to find a solution for the following task: All users (1) that have a game (2) with their items count (3) and items price (4) are to be retrieved by a query => Display ...
0
votes
2answers
258 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
0answers
25 views

Multiple join criteria with different keys?

I am having a hard time formulating this question for a targeted search and so none of the results I am getting seem to describe my exact situation. I work in healthcare and our tables are enormous, ...
8
votes
1answer
288 views

What cases benefit from the Reduce, Replicate, and Redistribute join hints?

The From Clause Documentation starting with SQL Server 2008 briefly mention 3 join hints and their basic mechanisms: Reduce Replicate Redistribute However there does not seem to be much information ...
2
votes
2answers
1k views

How to join global temp tables in SQL Server?

I have four global temp tables (##A,##B,##C,##D) on which I want to perform a left join in SQL Server 2008. By global temp tables I mean I created them using a code like this: SELECT * INTO ##A ...
0
votes
3answers
33 views

What is wrong with this syntax of a multi-table DELETE…ORDER BY?

I have the following query: DELETE proxies, pxpl FROM proxies JOIN proxy_xref_proxy_list AS pxpl ON proxies.ID=pxpl.ProxyID WHERE pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR) ORDER BY pxpl....
0
votes
0answers
17 views

MySQL: Use a LIMIT for a JOIN table added

View The view of the app contains a page which shows a bunch of carousels (each carousel = a tag), each of each should contain 5 products: +-------------------------------------+ |Page ...
1
vote
3answers
16k views

MySQL join two large tables is very slow

I have two tables, one of them contains a history of downloaded urls, while the other table contains details about each url. The following query groups the URLs by the number of repetitions during ...
0
votes
1answer
671 views

MySQL get previous row with inner join

In my query, I need to get the previous row with the current row and everything works fine here. SELECT *, (SELECT calendar_id FROM calendar AS sub WHERE sub.calendar_id < ...
1
vote
1answer
35 views

CASE in WHERE clause - retuns a single-row result instead of 4 rows?

Everyone, I do not want to bother you with the whole tables schema so I will paste a couple of screenshot only so that my question becomes clear - I need to show all projects that are related to ...
1
vote
1answer
52 views

Why does MySQL consecutive JOIN give an unexpected result?

I had 3 tables which were Category, Product and Vendor. Then I tried to make a consecutive join 3 tables in 1 query to count the product for each category. If a product or it's vendor is deleted, it ...
0
votes
0answers
19 views

Will PostgreSQL hash all columns

I have a large PostgreSQL database running on a server with limited memory. I often use inner joins on only one (unique) column as a way of filtering (is this bad practice?). I’ve noticed that the ...
1
vote
1answer
17 views

How to JOIN a table with itself while using a sub-query?

I JOIN a table to itself as SELECT t1.TagID AS TagID1 ,t2.TagID AS TagID2 ,COUNT(1) FROM TagMap AS t1 JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID GROUP BY ...
1
vote
1answer
136 views

Left outer join to itself to find outliers?

I have a table of item types and the coordinates of the items, like this: ItemType | X -------------- 1 | 1 1 | 5 2 | 5 1 | 6 2 | 6 ...
1
vote
1answer
26 views

Count nested joined table records only if the table associated via bridge table have two records with given names

I have the main table on which I have to apply count. I have two bridge tables and two nested tables for has_many relation. You can generate tables and bridge tables with below code and also insert ...
25
votes
3answers
50k views

Can I provide a default for a left outer join?

Suppose I have tables a (with column a1) and b (with columns b1 and b2) and I perform a left outer join SELECT * FROM a LEFT OUTER JOIN b ON a.a1 = b.b1 Then b1 and b2 will be NULL where a value of ...
16
votes
4answers
54k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
1
vote
1answer
18 views

UPDATE table element using data and id from another table

I need to update an element the row for id=N in table A with derived data from Table B. Table A has uid as its primary key, and contains DATE element foo. Table B has a_uid as an element, for each ...
-5
votes
1answer
41 views

Need help with the following SQL query [duplicate]

I need help with the following output. Tried case, Distinct, Group By, failed. Join might help, but still couldn't write a query with join.
1
vote
1answer
43 views

Are there better alternatives rather than using a “Full outer join” when it is not possible to use a “left or right join”?

I have two tables with below structure : Create table Modern_transaction (Eeffective_date Date, Customer_Num Number, Branch_Code Number, MTrns_type_one Number, ...
0
votes
1answer
571 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 slow ...

1
2
3 4 5
34