Questions tagged [join]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
746 views

Costly HASH MATCH Aggregate

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan CREATE ...
0
votes
1answer
112 views

What exactly happens when optimizer_prune_level = 1?

This documentation link states that optimizer_prune_level Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. Can ...
0
votes
1answer
20 views

Get top 5 students for each class in many-to-many relationship

So I'm doing this in MySQL. I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their ...
-1
votes
0answers
11 views

How can I get separate records when group_concat is applied

I have this huge query which used to get the details of orders rang and convert the price according to the currency. SELECT *, YEAR(o.date_added) AS year, QUARTER(o.date_added) AS ...
0
votes
1answer
80 views

What and where to index, rapid increasing table postgres

I work as a software engineer with python and django. Currently I am struggling with a design choice made before my time. We have a transaction table that logs all customer activity. Due to the ...
0
votes
4answers
132 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 alternatename....
0
votes
2answers
195 views

How to avoid conditional join in order to write a tsql view

I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables Roles table RoleName --------- Admin Viewer User Set table Id Name --- ---- 1 Employee Data 2 Help ...
0
votes
1answer
112 views

How to get only one acl_groups_to_resources.bitmask for a given set of parameters?

I have the following tables in my DB: CREATE TABLE `restricted_components` ( `component_id` int(11) NOT NULL, `resources_id` int(11) NOT NULL, `component_dom_id` varchar(50) NOT NULL, `...
-1
votes
2answers
27 views

How to do left join , same table with different conditions per column

Hi i need information about how to merge both sentences Sentence 1 select idlote from polizamovtos group by idlote having sum(cargo)-sum(abono) > 0 Sentence 2 SELECT SUM(cargo) as saldo FROM ...
0
votes
2answers
169 views

changing from inner joins to left joins to include null values?

I need to have this query return all NULL Values for hours for each jobcode. I have tried to change the inner joins to left joins, but the output is the same (only showing the 1 jobcode for one date ...
0
votes
2answers
54 views

LEFT JOIN not working as intended

I'm using mysql 8.0.21. I'm trying to join two tables such that I can find the index name of foreign keys. This is my query: SELECT KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA, KEY_COLUMN_USAGE....
0
votes
1answer
79 views

MySQL show data from Rows to columns

I am stuck at getting a query with joining 2 tables. Below is the database structure. What I want to achieve is to list all the information from one table with join on the second table but it has 3 ...
1
vote
1answer
55 views

Multiple roundtrips vs cross joins

I have a feeling the answer is "it depends on...", but I'm wondering if there's a concrete answer. In Entity Framework, you build a query using C# code, and the framework converts to SQL and ...
0
votes
1answer
9 views

How to Fetch A Record If Shipping And billing Address are Different

Show New Orders with Address If shipping and billing are different. I am stuck with this query. Can someone guide me a bit. Thanks
-1
votes
2answers
27 views

Do these joins change anything [closed]

I have a request which looks like this : SELECT A.* FROM A INNER JOIN B ON A.id_B = B.id INNER JOIN C ON B.id_C = C.id A.id_B and B.id_C are foreign keys NOT NULL For me the INNER JOIN ...
0
votes
1answer
85 views

nontrivial joining a relation with itself

I am a philosopher and am doing my first steps in relational algebra. So here is a puzzle: Let say that we have a relation R with attributes 'R.a' and 'R.b'. Is it possible to make a renaming and ...
0
votes
1answer
13 views

How to perform multiple joins onto a table and then join resulting table onto multiple columns into another table

| family_id | parent_id_1 | parent_id_2 | ----------------------------------------------- | 1234 | 9999 | 9567 | ----------------------------------------------- | ...
1
vote
1answer
28 views

Why Am I Getting Inconsistent LEFT JOIN Results in MySQL

Background I have a database in 3 different locations with the same exact static data (nothing updates it, it was a one-time import from another system). In 2 of the 3 locations, a query similar to ...
1
vote
1answer
722 views

Combining GROUP BY and JOIN

This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the ...
-2
votes
1answer
34 views

SQL JOIN query problem [closed]

I'm doing SQL class on Khan Academy and got stuck somewhere in the JOIN query section. Two tables: customers table with id(pk),name, email Orders table with id(pk),customer_id, item, price Question: ...
0
votes
2answers
52 views

Multiple group by with inner query in Oracle

I have a table Record with three columns: p_id, score, city. P_id score city 12 20 Mumbai 12 80 Delhi 12 50 Pune 13 20 Mumbai ...
0
votes
3answers
34 views

mySQL foreign key and primary key

Consider table A with columns id (primary key), name and table B with columns id, a_id (foreign key linked with table A id column), address. What will be the sequence of columns if the query is: ...
1
vote
3answers
157 views

MySQL slow performance and timed out after executing join

I'm not a MySql Server DBA. Two years ago I've created web service that send messages and inserts responses from remote web server in MySQL database (Server version: 5.6.16 - MySQL Community Server). ...
5
votes
2answers
112 views

Does Postgres optimize this JOIN with subquery?

In Postgres 12, I have a table purchase_orders and one for its items. I'm running a query that returns PO's for a given shop and a sum of items ordered on each PO: SELECT po.id, SUM(...
1
vote
1answer
120 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
131 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
69 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 ...
2
votes
1answer
514 views

Performance: where before or after join

I am doing a query like this: select * from table1 join table2 on table1.fieldjoin = table2.fieldjoin where table1.field = 'y' I would ask is this query equivalent in terms of ...
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
47 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
509 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
88 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
242 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
55k 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
18 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
261 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
9 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
0answers
16 views

updating n number of records mysql database

I want to update n number of records in table using a query like below, but it will return an error since in mysql, it is not allowed to use value after the limit key update producedItem pi inner ...
0
votes
1answer
262 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
111 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, ...

1
2 3 4 5
33