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
27 views

Select query with multiple joins

I have the following tables: ORDERS: OrderID(VARCHAR), CreatedDate(DATETIME), QuoteID(INT), BasketID(INT) BASKETS: BasketID(NUMBER), ItemID(NUMBER) ITEMS: ItemID(NUMBER), QuoteID (NUMBER), ParcelID(...
user avatar
0 votes
2 answers
48 views

Columnar DBMS and issue understanding multiple where constraints

I read a lot about columnar DMBS and perfectly understand that they are great for researches on a column. This question has a partial answer. Now, say I have a DMBS with 3 columns A, B and C. Doing a ...
user avatar
  • 101
0 votes
1 answer
46 views

Finding rows where totals do not match

MariaDB MySQL Trying to do what seems like a basic query (join) on two tables. Receipts id date supplier total Parts id receipt_id partType subtotal Trying to get the row from the first table when ...
user avatar
  • 3
0 votes
2 answers
58 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 ...
user avatar
-2 votes
1 answer
29 views

Difference between these two query implementations of inner joining a table with a lookup table?

I've been learning querying and I've come across the concept of inner joining Where this query inner joins the columns departmentId from employee and department tables and displays the value of ...
user avatar
  • 1
0 votes
1 answer
91 views

How return list with inner list in SQL? [duplicate]

I prepared following SQL statement: SELECT tasks.name, profiles.email, types.specification, tasks.description, tasks.attachment_link, tasks.priority, assigned_tasks.progress_details, ...
user avatar
1 vote
2 answers
34 views

Multiple left (I think) joins to obtain unrelated records from junction table

I have a database in which many-to-many relationships between records (e.g., customers, suppliers, products) work through a junction table that contains the ID (a unique code (i.e., no two records in ...
user avatar
  • 13
0 votes
0 answers
118 views

Insert checking in another table if value exists

My problem is the following: I have 2 Tables T1(idName) and T2(idName, name). I need to do an Insert in T1 with only the name and that the query compares if the name exists in T2 and get the idName to ...
user avatar
0 votes
1 answer
74 views

Return unique records based on shared column in 3 tables

This is the database schema that I'm working with and I've included some sample data as an example of what is currently in the database. CREATE TABLE Visits ([ADATE] date, [AKEYTIME] varchar(10), [...
user avatar
0 votes
1 answer
79 views

MySQL: Reason for Degraded performance of a single inner join

We have two tables in our MYSQL 5.7 Aurora database: CUSTOMER_ORDER and BATCH. Customer order can have only one batch associated and it is not mandatory to have one. Create table statement of ...
user avatar
0 votes
0 answers
38 views

Fetching result from many to many relation tables based on condition

Working on creating a conditional filter query for the following tables. Created a fiddle with data here. Desired output I want to write a single query which first fetches list of all products from ...
user avatar
0 votes
2 answers
76 views

How to UPDATE faster with GROUP BY?

I have a typical article and tag_map tables, CREATE TABLE `articles` ( `ID` int(11) unsigned NOT NULL, `Tags` tinyint(3) unsigned DEFAULT NULL, `Title` varchar(512) DEFAULT NULL, PRIMARY KEY (`...
user avatar
  • 4,261
0 votes
1 answer
29 views

Select column from current table to a related table

I have this schema: CREATE TABLE `players` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`) ); CREATE ...
user avatar
  • 181
6 votes
2 answers
3k views

PostgreSQL update join vs SQL Server update join

I recently started converting a personal project from Microsoft SQL Server to PostgreSQL and I was surprised at the abysmal performance I encountered doing an UPDATE JOIN between two tables. Suppose ...
user avatar
  • 181
1 vote
2 answers
175 views

LEFT JOIN not joining the second table

I'm having a problem with my SQL command running on MariaDB. I have the following tables: reservations: id child_id time_from time_to user_id 1 1 2021-05-17 13:20:00 2021-05-17 14:20:00 NULL 2 2 ...
user avatar
  • 13
0 votes
2 answers
2k views

Get the highest parent id recursively in MySQL

I want to find the uppermost parent ID for each entry. For example, CREATE TABLE t1 ( ID int(11) unsigned NOT NULL, ParentID int(11) unsigned, PRIMARY KEY (ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ...
user avatar
  • 4,261
0 votes
0 answers
50 views

How does MySQL handle indexes and where clauses with joins

I am querying large tables with joins and where conditions. An example query: SELECT * FROM forename JOIN forenameLength ON forenameLength.nameRef = forename.nameRef WHERE forename.placeRef = 18000 ...
user avatar
0 votes
0 answers
52 views

Filter joined rows if column's value exists in another table

Today I have encountered a problem as follows (fiddle here): create table ta02 ( t_key varchar(255) default null, ta_over_amount decimal(13,2) default null, name varchar(255) ...
user avatar
  • 109
0 votes
1 answer
48 views

Outer Join on all columns if possible, or a subset of columns if not possible, and sum

I have two tables, call them T1 and T2 each with 4 columns A, B, C, Quantity. I want to do an outer join. However, some ABC combinations in T1 do not match with T2, and vice versa. My goal is to join ...
user avatar
1 vote
4 answers
1k views

Why does FROM multiple tables default to cartesian product?

What was the idea behind doing a cartesian product (cross join) when i did a query like - SELECT * FROM agents, orders I would think that they would concatenate (like pandas). It feels more natural ...
user avatar
  • 129
0 votes
1 answer
29 views

Join with keys of both tables [duplicate]

I try to bring to tables together in a join-like way. Consider the column id_ as "on clause" key. In the tables below, there are more id_s in table 1 than 2. I would like to have all ids and ...
user avatar
0 votes
1 answer
79 views

Return results from SQL JOIN even if criteria not met in all tables

New to SQL. Building my first contact manager project. Current schema can be seen below. Issue: I'm working on a view that shows all contact types (phone, email, socials, address) for a given contact....
user avatar
  • 111
-1 votes
2 answers
885 views

MYSQL: Query the last conversion on chat messages and sort by latest message

I am having issue on query sorting of last convo messages of each users. Here is my current record on my 2 tables: I want to achieve below result: My current query is: SELECT `messages`.*, `users`.`...
user avatar
-1 votes
2 answers
573 views

SQL Server INNER JOIN multi-part identifier could not be bound [closed]

I have the following statement where selecting 2 tables from a different server and doing inner join but getting multi-part identifier could not be bound. select * from [SQ05].[mov].[dbo].[...
user avatar
  • 109
-1 votes
1 answer
376 views

Match closest date from a table to a table with just dates

I am trying to feel some gaps in table with values from a previous date. I have created a date table which looks like this: date 2021-04-13 2021-04-14 2021-04-15 2021-04-16 2021-04-17 2021-04-...
user avatar
-1 votes
1 answer
97 views

What is the correct way to organize tables for a SQL view?

I'm new to data modeling. Currently working at creating a personal contact manager but I need some help with modeling the table relationships for a given view. The final view columns I'm shooting for ...
user avatar
  • 111
0 votes
0 answers
37 views

INNER JOIN taking so long for even simple query

My inner queries when executed independently works fast(<2 sec). I have then applied INNER JOIN on two sub query results and final output takes around 25 minutes. SELECT DISTINCT tableA.consumer, ...
user avatar
1 vote
1 answer
380 views

Joining three tables with null values in SQL

I have a BOOKS table, a USERS table, and a RATINGS table (with a book id, a user id, and a rating value). For each book, I would like to be able to get an array with all the users and their rating for ...
user avatar
1 vote
1 answer
371 views

Invalid Column Name on CONCAT_WS

Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue. I have two tables cases and legal_entity the data on the two tables looks like this legal_entity ...
user avatar
  • 113
0 votes
1 answer
35 views

table organization with data that will never change

for reference, almost all my DB experience is with mongodb and this is my first postgres project. I'm storing data updates from decentralized finance crypto pools. The data structure I'm exploring is ...
user avatar
  • 217
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 ...
user avatar
0 votes
1 answer
24 views

Group by unique pairs with no multiplicity

I have a table AreaLocation with AreaID as the PKey and Location whereby there are many locations for each AreaID. Correspondingly there is another table Books with: BookingID | DriverID | BLocation | ...
user avatar
0 votes
1 answer
26 views

Checking if entities match an id in on relation where it could show up in either of two columns

The problem I'm trying to make a page for a website (with PostgreSQL database) that allows the owner of a group to add new members from their friends list. Given the owner_id of a Group its group_id I ...
user avatar
  • 103
2 votes
3 answers
379 views

Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's ...
user avatar
  • 21
1 vote
1 answer
678 views

Selecting data from a mapping table using a foreign key

I'm trying to make a simple Login sys where each user should be shown data from a list depending on their user ID Using MS SQL. After searching for how to do it best i came across Mapping (pretty much ...
user avatar
1 vote
1 answer
84 views

Can CTE simplify repeated and possibly recursive joins?

I have three tables organized as depicted below, showing foreign keys and unique fields (green): That represents email messages received by a server. (Sorry I couldn't simplify more. Full version ...
user avatar
  • 115
1 vote
2 answers
53 views

Can text fields be not loaded for join memory purposes in MySQL?

I want to use many text fields for a table but they can be very large so doing complex operations with rows of this table, like joins with big tables can become intractable or very slow I understand ...
user avatar
1 vote
3 answers
220 views

How to write "and" queries for a lookup table when you can't use the AND keyword?

I'm building a practice website that allows users to search hotels based off of the amenities that the hotel provides. From the FE, the user will select a checkbox for however many amenities they want,...
user avatar
  • 11
0 votes
1 answer
86 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 ...
user avatar
  • 131
0 votes
1 answer
335 views

Join three tables with max date value

I'm trying join three tables: hydrants ----------------- fid | h_number | ----------------- 1 | 2525 | ----------------- hydrats_survey ---------------------------------------------- fid | ...
user avatar
0 votes
1 answer
69 views

Capture row with highest value from each percentile

So far I've tried doing this: SELECT MAX(buckets.position_id), NTILE FROM ( SELECT position.position_id,NTILE(100) OVER (ORDER BY position.position_id) FROM position WHERE activity_id = 8 ...
user avatar
0 votes
0 answers
64 views

Improving query performance with two inner joins on tables that have > 40M rows

Context I have three tables - nodes, edges and sections. This is a graph network of paths from open street maps where edges are created from two neighbouring 3D nodes (that give distance, elevation ...
user avatar
0 votes
0 answers
27 views

Store Procedure Two different database and join help

I try to write store procedure that ı have a question.Firstly I have two db in same local server.One of them is hfcenta_test db has agency old datas and it has country code column with country name....
user avatar
  • 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 ...
user avatar
-1 votes
3 answers
56 views

LEFT JOIN Clause

I think it would be better for me to just ask why it is that we use the LEFT JOIN clause for the following task? List all manufacturer name and phone number that have poor sales… less than 3 sales of ...
user avatar
1 vote
2 answers
49 views

Under what circumstances would these 3 queries return different number of results?

Hello I have the following question: Query 1: SELECT b.id,     (     SELECT SUM(cb.invoice_amount) AS sum     FROM payment_lines cb     WHERE cb.batch_id = b.id     ) AS batch_total_invoice_amount ...
user avatar
1 vote
1 answer
73 views

why should I join 2 tables with 1 table apart

I could not solve this problem on hackerrank , and had to look up the solution. I was joining the wrong tables. There are 4 tables: hackers challenges submissions made by hackers and their scores ...
user avatar
  • 455
0 votes
1 answer
43 views

Leaf Joins in Data Warehouse Design

Upon modeling my very first data warehouse model (for historical stock market data), I stumbled across a data relationship that I don't know how to best account for. The fact tables include multiple ...
user avatar
0 votes
2 answers
78 views

Explaining what the OR operator does in the following code

I ran into the following SQL code which (successfully) outputs a list of parents and the age of their youngest child: SELECT parents.name AS name, MIN(children.age) AS age FROM people children INNER ...
user avatar
  • 103
1 vote
1 answer
579 views

MySQL JOIN COUNTS from another table

I have this two tables: Table members id full_name sex 1 John Smith 1 2 Carol Danvers 2 3 Clark Kent 1 4 Lois Lane 2 5 Diana Prince 2 Table member_emails id member email 1 1 ...
user avatar

1 2 3
4
5
38