Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,889
questions
0
votes
1
answer
171
views
how to get data from two tables in mysql query with where Pk of one table is set as foreign key on PK column for other table?
The query I am using to retrieve data is as
"select sum(totalbill) as tbdb, sum(discinamount) as tddb, sum (billafterdisc) as tbaddb,"
+ " sum(totalqt) as tqdb,"
+ " sum(payed) as tpdb,"
+ " sum(...
CommunityBot
- 1
1
vote
2
answers
350
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 ...
CommunityBot
- 1
0
votes
1
answer
241
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 ...
CommunityBot
- 1
-2
votes
2
answers
204
views
Postgresql - LEFT JOIN error
Apologies ahead of time for the novice question as I am working through building my first database. I've come to joining my tables (I have multiple), but have decided to test one first. I am trying to ...
- 1
1
vote
1
answer
553
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"...
CommunityBot
- 1
0
votes
1
answer
223
views
Join query with IN condition and Group_concat
I have an issue to get concatenated text from below two tables(product_to_category & category_description)
I need mysql query to get output as "Electronics,Electronics Component Connectors,...
CommunityBot
- 1
2
votes
1
answer
8k
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 ...
CommunityBot
- 1
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
2
votes
2
answers
936
views
Should I use left join to do my job in this scenario?
What I want to do is to find out the items which appeared more than once in this table:
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field ...
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) ...
- 69.5k
0
votes
1
answer
793
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 ...
CommunityBot
- 1
10
votes
2
answers
57k
views
Apply INNER JOIN only if there is a row available otherwise use NULL
+----+-----------+-----------+-------------+----------+
| id | value_id | parent_id | dropdown_id | name |
+----+-----------+-----------+-------------+----------+
| 7 | 14945 | 14944 | ...
- 107
0
votes
2
answers
147
views
UNION SQL with LEFT JOIN
I was trying to do the UNION with two Queries and i received 42601 error. Can you help me to undersant how to fixed it?
CODE :
SELECT Date_part('year', pr.data_chiusura) AS anno,
...
CommunityBot
- 1
0
votes
2
answers
18
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
...
- 123
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
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 ...
- 27.5k
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
0
votes
3
answers
283
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 ...
CommunityBot
- 1
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 = ...
- 89
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 ...
- 69.5k
-1
votes
0
answers
23
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
... ...
- 25.6k
0
votes
1
answer
507
views
What is the best way to handle a join / group_concat query in MySQL
I am looking for some feedback regarding the best way to handle the following (simplified for this example) situation.
I have 3 tables: tasks, assignees, and users
---tasks----
id, task, completed
---...
CommunityBot
- 1
0
votes
1
answer
408
views
Trying to join 2 tables to get a count of orders and grouped by email
I have two tables in DB Orders with columns Orders Id, Orders NO, Customer ID; Customer with columns Customer ID, Customer email. I need a count of the orders for each customer and group by email ...
CommunityBot
- 1
0
votes
2
answers
301
views
Join multiple tables without duplicated results and without UNION in PostgreSQL
Lets assume we have two tables A and B in PostgreSQL:
A
title
first_group_id
second_group_id
My Title
1
2
B
group_id
element_name
1
First
1
Second
1
Third
2
Fourth
2
Fifth
I want to get the ...
CommunityBot
- 1
0
votes
1
answer
307
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 ...
CommunityBot
- 1
-2
votes
0
answers
13
views
postgresql : how to join multiple relational tables and select row based on data in other tables [closed]
This is the description of my problem on this page
trying to get data like this with sqlalchemy didnt work for me :
tod_session_req = await session.execute(
select(
...
- 72.1k
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 ...
- 478
45
votes
7
answers
87k
views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
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 ...
0
votes
1
answer
277
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 ...
CommunityBot
- 1
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 ...
- 1,522
0
votes
1
answer
340
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.
...
CommunityBot
- 1
0
votes
1
answer
270
views
Slow query Mysql with Order By and JOIN
I've been investigating an issue with a slow query when trying to get the last 50 comments of a user.
To give some context the database size is 9 TiB and the COMMENT table has about 7 billon of rows.
...
CommunityBot
- 1
1
vote
2
answers
2k
views
Equality in JOIN or WHERE clause? Efficiency and style
Of the two queries below, which is the most efficient for postgresql?
And which has better style in terms of readability, etc.
The difference is the placement of the statement doctor.type != 'surgeon'...
- 154k
0
votes
3
answers
314
views
Subquery or Inner Join?
I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions.
SELECT t.id, t.date, t.amount, t.description FROM ...
CommunityBot
- 1
3
votes
2
answers
10k
views
Why does COALESCE function not work for this query?
I am trying to count the number of columns that is retrieved from the subquery, and some subqueries may be empty. So instead of displaying it as an empty cell, I want it to be 0. Found out that I can ...
- 154k
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
0
votes
2
answers
192
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` (
`...
CommunityBot
- 1
0
votes
1
answer
2k
views
How to remove NULL values in multidimensional array?
SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid
,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as ...
CommunityBot
- 1
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 ...
- 1,522
0
votes
2
answers
364
views
MySQL - Many to Many relationship, or one to many with additional columns
Our product contains some constraints that made us contemplate which table design will yield the best performance.
Our model:
Question table which contains fields like id, body, difficulty, and tags.
...
CommunityBot
- 1
1
vote
2
answers
955
views
MySQL: Data pagination according data on another table
I have a database with the following structure
Items table: itemID, itemName
Ads table: AdsId, itemID
I would like to make pagination on items table, 10 items in each page, but first I must retrieve ...
CommunityBot
- 1
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, ...
- 69.5k
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 ...
- 72.4k
0
votes
1
answer
143
views
Arabic language search in codeigniter error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
Arabic language search in codeigniter error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
In English language search is working
JOIN Query to use to ...
CommunityBot
- 1
0
votes
1
answer
411
views
How to optimize UPDATE with a nested SELECT subquery?
I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do:
In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
CommunityBot
- 1
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?
...
- 69.5k
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 ...
- 69.5k
-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?
- 103
0
votes
1
answer
293
views
How do I join a parent table to two child tables without duplication?
parent table
id
city_name
zip_code
1
new york city
10001
2
los angeles
90001
3
chicago
60007
Customer, child table
id
customer_name
city_id
address
1
alice
1
428 Hartford Lane
2
betty
1
249 ...
CommunityBot
- 1