Questions tagged [join]

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

Filter by
Sorted by
Tagged with
1 vote
2 answers
734 views

Join the SAME tables from DIFFERENT databases

I have an unusual situation where I have a bunch of backups of a mysql database and I am interested in selecting the distinct members of a given table from these backups (since some records might have ...
1 vote
2 answers
378 views

Why does SQL Server require a LEFT JOIN to retrieve a status that doesn't exist?

I was recently querying our internal database inventory tool for a list of servers, instances and databases, and was adding the corresponding status to each server, instance and database. Relationship ...
1 vote
1 answer
277 views

Getting the latest timestamp in Oracle

I am trying to create a report that has the latest member id in the first column and the old member id in the second column based on the timestamp in audits table below. Member ids are stored in ...
0 votes
2 answers
2k 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
7k views

Need help with left joins to avoid duplicate rows while appending column from another table in MariaDB?

I have this query which is meant to output the guest's name, how much they paid and the description for the associated item they have paid for. SELECT g.first_name, ex.pmt_method, ex.pmt_date, ex....
1 vote
2 answers
2k views

Mysql: Create a view with multiple self joins without duplicates in result

Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you. So i have a table entities_attributes_values where an ...
0 votes
1 answer
453 views

Joining without a unique identifier

Payments PaymentRef Product PaymentAmount MerchantRef P01 ABC 100 MR01 P02 ABC 200 MR02 P03 XYZ 200 MR03 Refunds ReundRef Product PaymentAmount MerchantRef R01 ABC -10 MR04 R02 ABC -100 MR05 ...
0 votes
1 answer
2k views

Sql merge columns with same name

I have a table of hockey games and a table of teams. schedule: teams: I am trying to do 2 joins on the same column. I have a schedule of home and away teams in table 'schedule'. I am trying to ...
0 votes
1 answer
2k 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 ...
1 vote
1 answer
310 views

Count nested joined table records only if the table associated via bridge table have two records with given names

I have the main table on which I have to apply count. I have two bridge tables and two nested tables for has_many relation. You can generate tables and bridge tables with below code and also insert ...
0 votes
1 answer
1k 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
360 views

Simple Update Join much slower than it should be (MYSQL)

This is a simple Update Join that updates only about 100 rows: Update A INNER JOIN B using(id) SET A.active = 1 WHERE A.date > '2020' This takes about 30 seconds to run, despite the fact that: ...
0 votes
3 answers
446 views

Get the ID of a next greater/less Value

imagine you have follwing table given id value 1 5 2 3 3 7 4 1 Depends on your case you need the ID of the next smaller or greater value. So the select of ID=2 would give u either 4 or 1. How ...
1 vote
2 answers
297 views

What should I index when joining on a table with conditions?

I have a query like this: SELECT "main_table".* FROM "main_table" INNER JOIN "other_table" ON "other_table"."deleted_at" IS NULL AND "other_table"."id" = "main_table"."appointment_id" WHERE "...
-1 votes
1 answer
292 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
2 answers
2k views

Does JOIN condition work like a loop on the joined table?

Do JOIN conditions work like a loop? (like Foreach) and if so, does it run the loop on the first table (the one after SELECT) or the second table (the one after JOIN clause) ? Let's say you have the ...
0 votes
2 answers
3k views

Confusion over using LEFT JOIN on multiple tables

I may be misunderstanding something basic here, but I'm struggling to find this issue being explained in my research. Let's say we have a users table and other tables that relate to the user, let's ...
2 votes
2 answers
13k 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 ...
1 vote
2 answers
2k 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
395 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. ...
1 vote
1 answer
613 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
7 views

SQL: Perform join on multiple tables on Google BigQuery

Please, I am running a professional course in data analytics. Right now, I am on my Capstone project. But I am having challenge combining tables in BigQuery using SQL. The data is about bicycle trips ...
0 votes
0 answers
20 views

Performance Differences in JOIN query Between MySQL 5.7 and MySQL 8.0

Hello MySQL Community, I wanted to share some observations regarding the performance differences between MySQL 5.7 and MySQL 8.0, specifically related to join algorithms. In a recent project, I ...
2 votes
1 answer
508 views

Delete using join or using sub-query?

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where `id` in (select `deletable_id` from ...
0 votes
2 answers
515 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. ...
0 votes
2 answers
1k views

UNION or SELF JOIN?

I have a table to hold parent and child posts together. +------+----------------+--------------+-------------+------------+ | p_id | parent_post_id | child_status | post_status | post_title | ...
0 votes
1 answer
910 views

Detect with Left Join duplicates entries and return the first value, also return sum of duplicates entries

¡Hi!, i have a case with two tables than can have a lot of matches entries and the time of fetching increases a lot. This tables can be Table A: Employees ------------------------- | Name | ID | ...
0 votes
1 answer
46 views

Inner join by datetime columns

I'm working in restructure a partinioned table so I have two tables with the same schema, for ensuring the insert process was executed succesfully without change some value i want to validate that the ...
0 votes
1 answer
702 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 ...
1 vote
2 answers
61 views

Merging information from multiple rows into one row

I have three tables event, person and event_person_map event: id integer PRIMARY KEY event_name text event_date date person: id integer PRIMARY KEY full_name text email_address text event_person_map:...
0 votes
1 answer
1k views

MySQL get previous row with inner join

In my query, I need to get the previous row with the current row and everything works fine here. SELECT *, (SELECT calendar_id FROM calendar AS sub WHERE sub.calendar_id < ...
0 votes
1 answer
569 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 ...
0 votes
1 answer
14 views

Select distinct values from one table and join non matching values from second table to create new rows

I have two tables I'm trying to combine into one. They don't have a matching key besides the ID. I need to look up distinct value in table 1 (Zip) and add every value listed in table 2 (activity). All ...
0 votes
3 answers
342 views

Using JOIN to find duplicates in another table taking too long

Have a MySQL 8 database with the following tables. people id | mobile | name registrations id | people_id | other_stuff I am trying to find the mobile number and the number of times it's duplicated ...
0 votes
1 answer
1k views

Left Join with only one of multiple matches in the right table

I have a database, where every entry may have multiple names, therefore I made a name database. While all names will be displayed otherwhere I need a list of all main entries with one representative ...
20 votes
3 answers
9k views

Column name naming conventions and best practices

I would like some expert opinion on best practices when it comes to column naming. The background is that according to Wikipedia, the following syntax, SELECT ... FROM Employees JOIN Timesheets ...
3 votes
2 answers
985 views

Grouping values into buckets

I'm working a query and I wasn't sure if my current approach is the "best" approach. I got a table filled with values and a table with buckets that I use to group those values. There are 2 types of ...
1 vote
1 answer
883 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 ...
0 votes
1 answer
2k views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly slow ...
0 votes
1 answer
25 views

Try to duplicate column= attribute1 at time=0 horizontally even on rows where time!=0 for every unique ID

Given a table such as this, I want to create a table via SQL without doing window functions id time attribute1 attribute1atFirstRecordedTimeperID 1 0 a a 1 2 b a 2 0 c c 2 4 d c I tried SELECT ...
1 vote
1 answer
26 views

How do you only get data from the table with the foreign key using SQLite?

I want to be able to get data from the table that owns the foreign key as a primary key, only using data in the WHERE clause from the other table. I have tried an inner join and I am getting weird ...
14 votes
1 answer
10k views

Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?

I have the following tables (taken from the Sakila database): film: film_id is pkey actor: actor_id is pkey film_actor: film_id and actor_id are fkeys to film/actor I am selecting a ...
2 votes
2 answers
1k 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"...
1 vote
1 answer
1k views

SQL Get Users By One Group But Exclude Others

I'm trying to write a query that get's all users that belong to Group A, but excludes users that belong Groups B, C & D. Users can belong to multiple groups. I have the following query but it's ...
0 votes
1 answer
5k views

Costly HASH MATCH Aggregate

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan CREATE ...
3 votes
1 answer
245 views

Creating a Trigger in MySQL that has a JOIN

PROBLEM: I'm trying to create a TRIGGER that will copy records from 2 different tables (users, addresses) in database A to a single table (users) in database B. Basically, I'm trying to consolidate ...
1 vote
0 answers
58 views

Column information from View

Suppose I have this database CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(255) NOT NULL, Roll INT NOT NULL, CustomID INT UNIQUE ); INSERT INTO Students (StudentID, ...
1 vote
1 answer
4k views

Index is not used when joining two tables in a stored procedure

In MySQL 5.7, I have two tables: CREATE TABLE `periodholdings ` ( `stkcode` varchar(255) NOT NULL, `participantid` varchar(255) DEFAULT NULL, `participantname` varchar(255) DEFAULT NULL, `...
-1 votes
1 answer
4k views

How to return missing rows from LEFT JOIN in BigQuery

How can we get BigQuery to return the rows in a LEFT JOIN which exist in TABLE A but are NULL in TABLE B? -- find missing users. return rows which exist in A but not in B select a.user_id ...
0 votes
1 answer
56 views

When using JSONb for a complex hierarchy of references, is it better to Join by aggregating the IDs in the JSON, or have additional relations table?

Lets say we have several primary tables in our database (a, b and c), and then another (x) that stores a complex, only semi-predictable JSONb object that has references to all the primary tables. In ...

1
2 3 4 5
40