Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,889
questions
-1
votes
1
answer
12
views
Optimize 5 million records SQL query (use join or not?!)
I have a table for products that contains almost 5 million records.
I have a column for the product category (product_category). That it is for now of type INT(11) and it is an index and refers to ...
0
votes
0
answers
22
views
Improving performance with a common low-cardinality field
I have a database with multiple tables. Every table has a field named sourcename, which indicates basically which customer the data came from. There are only about ten possible values for this field ...
- 101
0
votes
0
answers
38
views
How to flatten/pivot "inner join" into a single row in a certain order?
Let's say I have 2 tables in MySql/MariaDb: competisions and participants. There can be multiple participants in one competisions.
I want to
a) do an inner join
b) sort the participants by score
c) ...
- 27
0
votes
2
answers
19
views
PostgreSQL JOIN SELECT null if record on one of both table not exist
I am having 2 tables with data as below:
tableA
========
1
2
3
4
5
tableB
=======
1
3
5
6
8
9
With this query:
select
coalesce(tA.id, null) as id_A,
coalesce(tB.id, null) as id_B
from
...
0
votes
0
answers
14
views
speeding up JOIN by LIKE or REGEXP
I have two tables of
CREATE TABLE titles
(
title_id int(11) unsigned NOT NULL AUTO_INCREMENT,
title varchar(255),
INDEX(title),
PRIMARY KEY(title_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE ...
- 4,261
1
vote
0
answers
16
views
Best way to create a View which includes counts for multiple one-many relationships?
Consider three entities - Forum, Thread & Topic, with the following relationships
Forum has many Threads
Forum has many Topics
I want to create a View forum_details that displays all columns of ...
- 185
-1
votes
1
answer
42
views
SQL Multiple CASE conditions with SUM and count Functions along with JOIN Condition
Kindly help me with the right syntax of mine, really appreciate it, if you could just edit my existing syntax into correct one without alternate options. thanks in advance
Table schema
CREATE TABLE ...
- 3
-1
votes
0
answers
24
views
How to join all members of GROUP BY groups?
Suppose I have a table (myTable) of datetime indexed objects:
dttm | price
----------------------------------
'2022-7-11 T00:00' | 10
'2022-7-11 T01:00' | 11
... ...
- 99
0
votes
1
answer
23
views
MSSQL - SELECT on data from JOIN reference
SQL Server being used to run this
I'm trying to pull information that could be variable.. Could you please check it and find the error?
SELECT
id = t.id
name = c.name
fav_flavor = ...
- 113
0
votes
1
answer
42
views
How to display last record per parameter?
I have a query in SQL server I use to get basic stats from two tables of sites and records associated with sites.
I'm having trouble figuring out how to ALSO create a column that displays the last ...
0
votes
0
answers
23
views
T-SQL, Fast self join on the next available date where dates skip weekends and multiple rows can have the same date
TL;DR,
I'll post back later with more info including table, view, and index definitions; before/after results; and query plans to help to answer this question.
I'm trying to use a self-join to a view ...
1
vote
1
answer
40
views
How do I SELECT elements using WHERE with another table's record values in WHERE statement?
Problem summary
Today part of my app crashed, but nothing else stopped with it. I need to find the first and last tracked message ids for each channel during the downtime. I log unix timestamps ...
- 13
0
votes
1
answer
24
views
Find records with join matching all join IDs
Suppose to have the following database setup:
Users (
id int primary key,
name varchar(255)
)
OrgUnits (
id int primary key,
name varchar(255)
)
Users_OrgUnits (
user_id int,
orgunit_id ...
- 137
1
vote
0
answers
52
views
Postgres: one-to-one join with duplicates and inequality matching
** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 **
I have two tables taken from two data sources,...
- 11
1
vote
1
answer
36
views
join condition order
Given a simple query like this
SELECT *
FROM table_a a
inner join table_b b on b.id_from_a = a.id
Does the order of the on clause matters? In the example above I always put the recently joined ...
- 113
0
votes
1
answer
30
views
How can I optimise this MySQL query?
I'm trying to make a filter which matches every record in the table to find ones which are within the range @min and @max. They're unxi timestamps if anyone was wondering.
It does work right now, but, ...
- 13
1
vote
1
answer
32
views
How can I correct this mysql join query and put 1 to many in the same row?
This query is close to what I need but google_responsive_headlines.responsive_headlines has many rows to google_text_ads. I would like to have those results in the same row but not sure how to ...
- 113
-6
votes
2
answers
46
views
How can I join 2 tables together, and then count by 2 columns? [closed]
Say I have these 2 tables:
What is the best way for me to join these 2 tables, and then count the number of userids per country, per category?
0
votes
1
answer
76
views
MySQL - Optimizing a large table's join
Im using the following query. This eat my complete CPU in Aurora Cluster. Also too much of wait. I tried by tunning index, but still no luck. Is there any recommendation to make it perform better?
...
- 1,708
0
votes
1
answer
30
views
how to display matching values from different tables which used in joins without duplicates
Here, I wrote a query using joins, the relation is fine, but I wanted display multiple columns from different tables which I used in joins, when I am trying to include or select other table columns it ...
2
votes
1
answer
36
views
Getting all shared values of two or more given values in one table (SQL)
I have a chat database where each user belongs to a chat; this relation is represented in the table "is_member(uid, cid)". Now, when creating a new chat, I want to check if there is not ...
- 23
0
votes
1
answer
36
views
Is it possible to have a join table where the right side is one of several possible tables?
We have a table meant to store a property of something else:
Table: property
Column: id, PK
Column: owner_id, FK
Column: name
Column: value
Column: kind
we generate a number of properties based a ...
- 123
1
vote
1
answer
69
views
Why is this simple query making up 50% execution time?
I'm using postgresql 9.5. I installed the pg_stat_statements extension to analyze queries and got the following surprising result:
scheduler=# SELECT substring(query, 1, 30) AS query, ...
- 133
-1
votes
1
answer
25
views
how to join these individual queries into a single query
I have two individual queries, which I have to join them into single query.
1st query:
I have a parameter called name in a request. When I give that name, it should determine its id. Example: If I ...
0
votes
0
answers
27
views
How to use column names stored in another table?
I have a few tables in which I store some table names, column names, and some data.
I need to get some column names from the content of these tables, and then use those names to create a bigger query.
...
- 135
1
vote
0
answers
24
views
Getting totals from different tables to create overall total
I currently have 2 tables TableA and TableB` both within the same MYSQL Database.
I am trying to find a way to take totals from both tables and add them together to give a definitive total.
The tables ...
- 111
1
vote
1
answer
50
views
Avoiding duplicates when joining multiple tables
Schema
create table computer (computer_id bigserial primary key, computer_name varchar);
create table computer_hdd (computer_hdd_id bigserial,
computer_id bigint references computer(...
- 253
2
votes
1
answer
48
views
Is it a bad idea to have a "kind" column on a join table?
Is it a bad idea to have a "kind" column on a join table or should I just create separate tables for each kind?
For example:
Table A: Person
Table B: Song
JoinTable A: Person_Song (Kind: ...
- 123
0
votes
1
answer
63
views
No column name was specified for column 1 of 'd'
I am trying to get some data from 3 tables in SQL Server, but getting an error.
I have created a fiddle with similar structure and sample data
Based on the data in tblData, I am trying to get the ...
- 135
-1
votes
1
answer
38
views
Optimization : JOIN or CTE [closed]
Suppose that I have these tables,and example data (the real table I am working on has more columns)
table `users`
id
name
zip
1
John
201
table `region`
zip
city
state
country
22
a
b
c
table `...
- 23
7
votes
4
answers
848
views
MariaDb - How to ensure a (LEFT) JOIN only if a single right side record exists
I have two tables I need to join together. However, I only want to join records when there are unqiue matches rather than picking one of several for the join.
Versioning:
Using MariaDB version 10.3.34
...
- 173
0
votes
2
answers
73
views
Why does Postgres not use an index when joining a materialized view?
I have a table defined as:
CREATE TABLE addresses (
"address" varchar(42),
"other_info1" text,
"other_info2" bigint
);
and I have some manual corrections to ...
0
votes
0
answers
46
views
Is there a cross join in these CTEs?
I have a query that is constructed from several CTEs (in order to organize the query). I constructed it in my SQL workbench and was happy with the test results on a smaller dataset, so I converted it ...
- 171
0
votes
1
answer
41
views
SQL join unexpected result
I have the following table:
item
running
resourceid
017510
C1
43
338877
C4
44
451233
C1
45
771225
C4
41
011212
C4
47
313366
C3
34
771226
C4
48
990000
C4
46
for each "running" I ...
- 11
1
vote
1
answer
84
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
...
- 43
0
votes
0
answers
18
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
33
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....
- 463
-1
votes
1
answer
16
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
31
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
43
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
2k
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
465
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, '...
- 425
1
vote
1
answer
115
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
24
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
322
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
291
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
0
votes
1
answer
53
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
465
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
68
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
562
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 ...
- 175