Questions tagged [join]

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

Filter by
Sorted by
Tagged with
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(...
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 ...
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 ...
-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 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"...
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,...
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 ...
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 ...
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) ...
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 ...
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 | ...
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, ...
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 ...
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 ...
-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 ...
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 ...
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 ...
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 = ...
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 ...
-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 ... ...
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 ---...
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 ...
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 ...
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 ...
-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( ...
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 ...
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 ...
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 ...
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. ...
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. ...
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'...
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 ...
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 ...
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,...
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` ( `...
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 ...
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 ...
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. ...
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 ...
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, ...
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 ...
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 ...
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 ...
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? ...
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 ...
-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
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 ...

1
2 3 4 5
38