Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,889
questions
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(...
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 ...
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 ...
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 ...
-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 ...
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, ...
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 ...
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 ...
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), [...
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 ...
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 ...
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 (`...
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 ...
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 ...
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
...
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 ...
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 ...
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) ...
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 ...
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 ...
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 ...
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....
-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`.`...
-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].[...
-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-...
-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 ...
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,
...
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 ...
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
...
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 ...
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
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 | ...
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 ...
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 ...
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 ...
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 ...
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 ...
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,...
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 ...
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 | ...
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
...
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 ...
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....
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 ...
-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 ...
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
...
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
...
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 ...
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 ...
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 ...