Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,858
questions
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
...
- 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 ...
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....
- 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
...
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 ...
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 ...
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
...
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, '...
- 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 ...
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
...
- 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. ...
- 3
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 ...
- 3
-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 ...
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 ...
- 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`) ...
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 ...
- 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 ...
- 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 `...
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] |
+--------------+-...
- 155
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 ...
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 ...
- 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", ...
- 1
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 ...
- 1
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 ...
- 9
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 ...
- 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 ...
- 101
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 ...
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 ...
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 ...
- 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": "...
- 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 ...
- 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}
...
-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 ...
- 1
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 ...
- 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 ...
- 33
-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
...
- 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 ...
- 391
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` = `...
- 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 ...
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 ...
- 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 ...
- 153
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....
- 125
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 ║
╠═══════...
- 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 ...
- 123
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 ...
- 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 ...
- 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 ...
- 1
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 ...
- 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 ...
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 ...
lottikkk