Questions tagged [join]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
3 views

SELECT only IS NOT NULL columns with LEFT JOIN

I have a query where i need to select product options from multi tables. SELECT p.option_id, p.product_id, p.sku, p.qty, p.price, c.color_label, s.size_label, m.material_label, v.variation_label ...
user avatar
  • 33
0 votes
0 answers
7 views

MYSQL -> SELECT, JOIN, GROUP BY to UPDATE

I get the data I need with the following select statement: SELECT tk.actiondate, tk.completedate, tk.closedate, MAX(com.date) AS realcomplete, MAX(clo.date) AS realclose FROM ticket tk LEFT JOIN ...
user avatar
0 votes
1 answer
28 views

Drop the constant recurring JOINs in SQL queries on a SQL-Server, is this possible?

I would like to show the columns of "linked" tables in SQL-Server database. In my particular case, there are two tables "Loads" and "Locations", linked by Loads....
user avatar
  • 333
-1 votes
1 answer
8 views

Getting 502 on join eloquent laravel

I need to get distinct value using inner join. Here is query SELECT distinct(ad_camp_sid.cid) from ad_camp_sid inner join ad_dsp_sites ON ad_dsp_sites.site_id = ad_camp_sid.sid ...
user avatar
0 votes
0 answers
19 views

Joining table to MariaDB OQGRAPH table returns no rows

I am having an issue with the OQGRAPH storage engine in MariaDB. When I select from it, specifying a hard-coded destid and latch = 'dijkstras', it returns the expected data; however, when I try to use ...
user avatar
0 votes
1 answer
38 views

How to optimized self-join view of 50 millions rows table

I've the following simplified table with ~50M rows. table sample ( id uuid not null primary key, measured_date timestamp with time zone not null, segment_id uuid not null, activity_id ...
user avatar
3 votes
2 answers
64 views

How to Join two tables with SUM and GROUP BY in SQL server

I have 2 tables product id designation 1 coca 2 Pepsi 3 Fanta 4 Seven 5 Eight 2)sub_product product_id name quantity 1 sm 10 1 lg 10 1 xl 20 2 1L 10 2 2L 20 2 5L 20 3 Ty 10 3 Sa 20 4 ...
user avatar
3 votes
2 answers
356 views

Can I write a FULL OUTER JOIN without OR IS NULL?

Here's some data to play with: CREATE TABLE a ( a_id int NOT NULL, a_prop text NOT NULL ); CREATE TABLE b ( b_id int NOT NULL, b_prop text NOT NULL ); INSERT INTO a VALUES (1, '...
user avatar
  • 415
1 vote
1 answer
75 views

Simple query with a single join very slow

I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
user avatar
0 votes
0 answers
19 views

CSV Resultset for IN clause

I am working on writing a SQL query to get data from 2 tables with inner join and need some help. Here is the scenario. I have 2 tables say A and B. table A Name Value 1 Red 2 Green 3 Blue 4 ...
user avatar
  • 1
0 votes
1 answer
47 views

child to parent in same table - there must be a better way to do this

i want to relate child to parent from same table. Table is built like this. id, parentid, name i want to have the complete path of the [name] columns without specify levels like i have in the example. ...
user avatar
0 votes
1 answer
41 views

Select a row using a foreign key in another table

Hi I'm using python and sqlalchemy and postgresql for creating a relational database I have 2 tables Users and Connections Users have information about each user with their id as primary-key and ...
user avatar
-2 votes
0 answers
22 views

Tool to generate random select queries?

I'm trying to test a few things performance-wise on postgresql. I specifically need lots of queries selecting random tables with random expressions doing multiple joins, similar to the join order ...
user avatar
0 votes
1 answer
21 views

mssql - query with results from same table and association table

I need help with a query. I have 3 tables: "Parts" table. one important thing to note here is that, the parts table contains both main part and child part. Child parts are components of ...
user avatar
  • 105
0 votes
2 answers
38 views

MySQL: SUM() returns multiple rows when used with joins and grouping

I expected by query, which uses SUM() to return one row, containing the total of all rows. However, it returns multiple rows. SUM() is not adding the rows. SELECT SUM(DISTINCT `sales`.`sale_shipping`) ...
user avatar
0 votes
1 answer
31 views

How do I get the sum of a field and avoid duplication from an inner join?

I am pulling Geo from the Accounts table and I am ranking the Geos by Opportunity $$ Amt based on Accounts that have the name like "Brinks%". Accounts are unique, and there can be many ...
user avatar
  • 3
4 votes
1 answer
304 views

Can CASE be used to select which table to JOIN?

I'm attempting to use a CASE expression to select which table each row should be joined with. Say I've got a table of item_instances in my game, of all the items in the game world, and some of the ...
user avatar
  • 165
0 votes
1 answer
25 views

MySQL: SUM() does not provide sum when used with JOIN

This produces one column with 10 numerical rows: SELECT (`sales`.`sale_shipping`) as `total_shipping` FROM `sales` LEFT JOIN `contacts` ON `sales`.`contact_id` = `contacts`.`contact_id` LEFT JOIN `...
user avatar
1 vote
0 answers
37 views

Join JSON column using an index

I have two tables: definition (7,000 rows): +--------------+----------+ | definitionId | nameRef | +--------------+----------+ | 1 | [1] | | 2 | [1,2] | +--------------+-...
user avatar
2 votes
0 answers
86 views

Why does SQL Server 2019 stored proc get Adaptive Join on Development Server, but not on Production Server?

I have a stored proc with one select statement that gets a plan w/ Adaptive Joins on my development server, but not on my production server. What could explain that? I'm not much interested in ...
user avatar
0 votes
1 answer
42 views

Optimize join without denormalization

I have two tables: orders(order_id, scan_timestamp) and order_products(order_product_id, order_id, pick_number, quantity) and I want to know how many of a particular product (identified by ...
user avatar
  • 415
0 votes
0 answers
20 views

Do most real world join conditions involve no more than 2 columns?

In the database application I have worked on, all join conditions are binary, like select * from A join B join C on A.id=B.id and B.orderno = C.orderno Each condition, separated by "and", ...
user avatar
0 votes
1 answer
23 views

Does Greg Rahn's "join order benchmark" reflect real world OLAP join scenarios?

Greg Rahn's "join order benchmark" has dozens of inner join queries on the IMDB database. Does this benchmark and the database itself reflect common real world OLAP join scenarios? How does ...
user avatar
0 votes
1 answer
70 views

Parallel merge join with sorting on large temporary tables in PostgreSQL

I have two tables with text column which I want to join: larger has billion rows and smaller has 100M rows. Tables don't fit into memory, so PostgreSQL reasonably uses merge join for joining. The ...
user avatar
0 votes
1 answer
22 views

MYSQL Joining 3 tables by sequence

I'm gonna try to joining 3 tables by sequence, Table 1 CREATE TABLE table_1(tb1_id int,rpp_id int); INSERT INTO table_1(tb1_id,rpp_id) VALUES ('1','127'); tb1_id rpp_id 1 127 Table 2 CREATE ...
user avatar
  • 3
0 votes
0 answers
16 views

Translation Fallback query

Hello I am trying to use the WordPress database without the WordPress therefore I'm writing some manual queries with Laravel's ORM eloquent, anyways I was trying to implement the fallback translation ...
user avatar
0 votes
0 answers
14 views

Standard solution to join 3 related data-group

i have 3 data-group stuffs - users - notifications each stuff for each user has deferent notification state and i dont know witch model is standard for connect them, I do not want to get into trouble ...
user avatar
0 votes
0 answers
10 views

SQL QUERY Counting Subsequent Inner Join

I am learning SQL for my DAD-220 class and hit a wall on formulating a query. I have tried searching and reading and thrown quite a few queries at MySQL but cannot figure the correct query. I have 2 ...
user avatar
1 vote
2 answers
34 views

Two OPENJSON, return each array element in separate rows

I have a table where I save in JSON format the quantity that exit and return of a product. The data saved in the two columns out_quantity and in_quantity is in array format. CREATE TABLE products_info ...
user avatar
  • 13
1 vote
1 answer
54 views

Unique array values for this string_to_array

This is a follow-up to: Best way to map different JSON keys to same target columns Based on these sample tables: data_providers: id | field_map -------------- 1 | {"segments": "...
user avatar
  • 203
3 votes
1 answer
54 views

Why does an index on the join predicate does not improve the performance of a hash join?

Various references, such as join-strategies-and-performance-in-postgresql, say that an index on the join predicate does not improve the performance of a hash join. However, couldn't a B+ tree index ...
user avatar
  • 33
0 votes
1 answer
18 views

Using Indexing When Performing JOINs on Part of a Composite Key

I have a table in my database (hosted on MariaDB) that looks like this (I have given the definition in SQLAlchemy), class Address(Base): __tablename__ = 'address' __table_args__ = {'schema': DB_NAME} ...
user avatar
-2 votes
1 answer
41 views

Print all the row with two or more element in the column

In following table how to print the student number when age occur two or more times, The result should be only column Sno i.e. Result Sno Reason 1 because 18 occur two times 2 because 20 occur ...
user avatar
2 votes
2 answers
212 views

Can you include the columns of two separate tables in the result of a sql query?

I am trying to order the results of a sql query of one table using the column of another table. Is it possible to include this column from a different table in my results? For example, this query uses ...
user avatar
  • 127
3 votes
3 answers
127 views

Use values from one column as column names and sum data from another table

I'm going to do my best to describe this. So I have 3 tables as follows: project id title department_id 1001 Project One 50 1002 Project two 50 metric id name 101 Number of exhibits 102 Number ...
user avatar
-2 votes
2 answers
23 views

Get count of unmapped records when using a mapping table

I have 3 tables as such Product Product ID Product Details 1 ... 2 ... 3 ... Vendor Vendor ID Vendor Details 1 ... 2 ... 3 ... Product Vendors Product ID Vendor ID 1 1 2 1 1 2 2 2 3 2 ...
user avatar
  • 107
1 vote
1 answer
20 views

Best practices for large JOINs - Warehouse or External Compute (e.g. Spark)

I am working on a problem that requires a very large join. The JOIN itself is pretty straightforward but the amount of data I am processing is very large. I am wondering for very large JOINs, is there ...
user avatar
1 vote
1 answer
77 views

How to index a query with many joins

I have this query with this schema that takes ~8s on my laptop and longer on the vm running the service. SELECT DISTINCT `mofs`.* FROM `mofs` INNER JOIN `isotherms` ON `isotherms`.`mof_id` = `...
user avatar
  • 111
-1 votes
1 answer
48 views

SQL query to join on maximum value on a per-row basis

I'm stuck writing a query for the following scenario: I have a table of things, they have a metric called number in the examples below. I have another table called offers which is a list of offers ...
user avatar
0 votes
1 answer
45 views

How to join 2 tables and fill non matching entries with a date

A company buys parts from other companies. To not overload their servers, every 5 minutes the price of 1 part is checked to see if a part has changed price. So a table contains the last time a part ...
user avatar
  • 25
0 votes
0 answers
59 views

Left join performing very badly

Consider the following situation, where the two tables below need to be joined. The query is currently performing very badly. There are no indices defined nor are the ids marked as PK. I was thinking ...
user avatar
1 vote
0 answers
17 views

Postgresql -- join syntax 'bug' that actually ran [duplicate]

I noticed I wrote a query like this, the day after I wrote it: SELECT T1.col1, T2.col1, T3.col1 FROM our_schema.table1 T1 INNER JOIN our_schema.table2 T2 LEFT JOIN our_schema.table3 T3 ON (T2....
user avatar
4 votes
2 answers
107 views

SQL join on an edited field

I'm trying to join Table1 and Table2 on the Url fields. However all the Urls in Table2 end with a "/" as per the example below: Table1 ╔═════════════════════╗ ║ Url1 ║ ╠═══════...
user avatar
  • 43
2 votes
1 answer
51 views

Slow query with multiple INNER JOIN (via table)

This is my first question here. I have a project written in PHP running on windows server and apache and mysql 8. There is functionality to show news by tags (many-to many). So I have three InnoDB ...
user avatar
0 votes
1 answer
27 views

Getting the last few rows of a large table with query that is using JOINs

I'd like to get the last (or last few) rows of a table (named CONTENT) containing more than 10M rows. The query contains joins on 2 other tables and it is extremely slow. These are the table ...
user avatar
  • 233
0 votes
1 answer
45 views

Convert right side of join of many to many into array of objects

I have 2 tables: Table subjects: id, class_id, title, created_at, updated_at Table marks: id, student_id, class_id, mark, created_at, updated_at, subject_id I want to query subjects table and have it ...
user avatar
  • 3
-1 votes
2 answers
35 views

I want to select many columns from many tables without foreign key

I have three tables, and I want to fetch the three table data without a foreign key. Say I have a table product_1 which contains (id, name) and table product_2 contain (id, name, price). I want to ...
user avatar
0 votes
1 answer
97 views

In Postgresql, can I create a column of a one-dimension array type with foreign key constraint? If not, how to simulate it?

In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be ...
user avatar
  • 5
2 votes
1 answer
40 views

Limiting the number of records from nested INNER JOIN table

The context is as follows: We have 3 tables: We'll call them A,B and C. A has a foreign key referencing table B, we'll call it "bId". The relationship is a ManyToOne. (One record in B can ...
user avatar
0 votes
2 answers
107 views

How is the SQL Query process in multiple Joins?

I have a query like this: SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.ID = table_2.ID INNER JOIN table_3 ON table_1.ID = table_3.ID How SQL process this joins? Does ...
user avatar

1
2 3 4 5
38