Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,999
questions
0
votes
0
answers
27
views
Would INNER JOIN on equality of two keys delete rows effectively?
Ī̲ repair a MediaWiki site after a DoS attack which injected terabytes of garbage into the text table. Ī̲ found that DELETE with clauses works painfully slow and, even worse, locks the table; that is, ...
1
vote
1
answer
59
views
How to efficiently query with a join and a where/order across two tables?
I have two PostgreSQL tables:
contents
id (primary key)
captured_at (date)
status (enum)
plenty of other columns we don't care about
library_contents
library_id
content_id
With three indexes on ...
1
vote
0
answers
399
views
database design - sales, products, customers, persons and organizations
I am trying to model a system where you can have persons or organizations as customers.
persons or organizations can also have other sub-types like employees, suppliers and other types in the future.
...
0
votes
0
answers
66
views
How to show all the columns data in full outer join
I have 2 tables.
The Result is
Issue is EnteredBy=23 is not displaying because I am using CT1.EnteredBy.
How to display CT11.EnteredBy value instead of NULL
The Actual Query I am using is given ...
0
votes
1
answer
42
views
Using the correct JOIN
I am trying to create an app for the salespeople who work for my company to keep track of their sales/commission
When a salesperson brings on an account, their name is noted on the account and the ...
0
votes
1
answer
66
views
Enforce query plan for queries with expensive predicates/UDFs
I have a question on how to enforce the query optimizer to use a certain execution plan. Lets assume we have two tables, t1 (25k rows) and t2 (100k rows). Also note that all the rows in t1 will find ...
0
votes
0
answers
62
views
How to subquery and FULL Join onto the same table
I have a SQL Table with alot of Team Names and Queries
For Example:
I want to do a FULL JOIN to compare the Red Team and Blue Team Only to see who is in Red, in Blue, or in Both. To Get Something ...
-2
votes
1
answer
40
views
non-recurring table join
I have 2 tables "orders" and "sales". The productCode field is common in this table.
My goal is to "join" 2 tables side-by-side as follows.
What I've Tried
partion by (...
-1
votes
2
answers
2k
views
SQL - Join column with condition if it is <> Blank
I want to join two tables together using ColumnA and ColumnB. However, ColumnB is sometimes a blank string.
If ColumnB is blank, I just want to to join using ColumnA. But if ColumnB is not blank, I ...
4
votes
1
answer
141
views
T-SQL: Iteration vs. Set-Based Operations When Independence of Ancillary Data is Needed
BACKGROUND
I am using Microsoft SQL Server Management Studio v18.9.1 on Windows 10.
I've created a database to store test results of our company's widget (not yet being produced). I am working on a ...
1
vote
1
answer
1k
views
SQL COUNT occurrence of column value on LEFT JOIN
I have seem similar questions on here but none of the answers seem to fit what I am looking for-
Say I have one table like so:
AssignedTo
DeptID
Type
Mary
5001
Elementary
Bob
5002
Middle
Bob
5003
...
1
vote
1
answer
2k
views
Saving output from INNER JOIN into a new table in MySQL
So I have a query in MySQL with an INNER JOIN which goes as follows:
SELECT * FROM `distributors` INNER JOIN `manufacturers` WHERE distributors.manufacturer_id = manufacturers.manufacturer_id
How do ...
2
votes
1
answer
128
views
Converting 2 select clauses into 1 select
I'm making a "bom" list entry into the "materials" table.
I bring the materials defined for the product group to the grid so that users do not forget the relevant material.
Users ...
0
votes
1
answer
90
views
Why im getting error 1114 the table is full?
This query works fine
SELECT MAX(customers.created_at) lst_trx_date, business_infos.id, business_infos.name as business_name, users.name as owner_name FROM users
INNER JOIN business_infos ON ...
0
votes
1
answer
77
views
Delphi-7 (150), and LocalSQL - the old BDE 32-Bit from BORLAND - INSERT INTO ErrorMessage
Okay, I know, that the BORLAND BDE-32Bit is a little bit Retro, but I would like using it, because I would like no install a complete Server Solution on my small Desktop PC.
Okay. Starting with this ...
0
votes
0
answers
198
views
Joining on a column where the value can be NULL in MySQL
I'm trying to unary join a table using an ID which can be NULL (here it's MID). Here's my code so far (names shortened for convenience):
SELECT s1.SName, s2.SName
FROM S s1
JOIN S s2 ON s1.MID = s2....
0
votes
0
answers
206
views
Efficiently Filter Parent Table with Join to Two (or more Children Tables)
Given the following schema for Microsoft SQL Server:
CREATE TABLE Parent (
TenantId int,
Id int,
ChildAId int,
ChildBId int,
);
CREATE TABLE ChildA (
TenantId int,
Id int,
);
...
-1
votes
2
answers
2k
views
SQL Server: left join resulting in multiple rows, want each row result in separate column in result
I need some logic help with a complex join situation. Here is an overall idea of the 3 tables I am working with
Table 1:
ID Number Type 1
A1
A2
Table 2:
ID Number Type 1
ID Number Type 2
A1
B1
...
0
votes
0
answers
93
views
Optimise query selecting latest result from result list for each client
I'm hoping to time optimise a currently working query for a large production healthcare database. My current code takes about ~30 seconds to run. MSSQL 2016.
Firstly, I'm selecting just patients from ...
1
vote
2
answers
440
views
Table Aliases: MySQL says table R1 does not exist when Joining the same table 3 times
I have a table R with the following fields stated below:
weatherID,
dateRecorded,
Days,
locationID,
timeRecorded,
rainAmount,
rainNumberHours,
temperature,
windSpeed,
windDirection
NB: The ...
-2
votes
1
answer
22
views
Extracting data from two tables
I have( for example) a table called A and a table called B.
Table A contains tons of user data( age, country bla bla) and is connected to B by forein_key=id( B only has ID/NAME.
I want to find out how ...
0
votes
1
answer
73
views
If a table column corresponding to different target columns in different tables, how to join this column in SQL?
While I was reading the documentation of sys.allocation_units, I found the following description for its container_id column.
ID of the storage container associated with the allocation unit.
If type =...
-1
votes
1
answer
271
views
Does SQL Server support join type "LEFT SEMI JOIN" in your query?
While I was watching this YouTube video, I found the author seems using SSMS.
I never know SQL Server supports the LEFT SEMI JOIN syntax, so I give it a try with the following code.
USE tempdb;
GO
...
0
votes
1
answer
51
views
SQL: Single VS multiple join tables
Let's say that we have the following tables:
user
team
meeting
Both a team and a meeting can have multiple users (1-to-many relationship).
What is the best way to model this DB structure?
Option 1:
...
3
votes
2
answers
2k
views
Join with comma separated values in SQL without direct relation between tables
I have a table [CourseMaster] LIKE
CourseId CourseName
-----------------------
01 ABC
02 DEF
03 GHI
04 JKL
05 MNO
06 PQR
07 ...
0
votes
2
answers
174
views
Bad practice? Use inner join id for future joined tables
I have 2 or more tables that will frequently be used for JOINs in database VIEWs but they don't have any field in common so I'm considering adding the same generated id key to be inserted into a "...
5
votes
3
answers
192
views
Select telemetry data based on relational data in PostgreSQL/TimescaleDB
I'm trying to solve a particularly difficult problem. I am storing some telemetry data from some sensors in an SQL table (PostgreSQL) and I want to know how I can I write a query that will group the ...
-2
votes
1
answer
123
views
Correct the code
Please help to correct the below code .
select *
from openquery(DRUPAL,
'select o.*
from (
select
max(vid),
nid
from content_type_irb_application_form
...
-1
votes
1
answer
441
views
Need SQL query to update the column using case when by joining two tables in Netezza Database
There are 2 tables : Sales & Transaction. I need to update the INDICATOR column of the Sales table using CASE WHEN by joining these two tables in Netezza database.
Using below query but not ...
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
2
answers
825
views
How to Select The Lowest Date in one field that's higher than the date in another field
The below aim isn't actually what my query is for, but I'm using it as an analogy to explain more simply what I am trying to achieve:
I am trying to build a BigQuery Script which looks at all arcade ...
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 ...
-3
votes
1
answer
1k
views
How select join sum with 0 returned if not exist
I need some advice. I want to select join with sum.
select year(date), month(mon), c.id, c.name, sum(t.trx_detail)
from client c
left join trx t
on c.id = t.id
group by year(date), month(mon), c.id, ...
0
votes
1
answer
393
views
MySQL query: Detect different rows between two tables and show differences in result table
I have two tables in a MySQL database.
Table1
a_id
1
3
4
5
6
Table2
b_id
1
2
3
I would like to spot the missing row from Table1 a_id 2, and missing rows from Table2 b_id 4,5,6, which should be shown ...
0
votes
1
answer
104
views
mySQL INNER JOIN and Conditions
I wrote a mySQL query that is just works as I need.
SELECT COUNT(wp_posts.ID) as count, wp_terms.term_id
FROM wp_terms
INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_id = wp_terms.term_id AND ...
0
votes
1
answer
50
views
LEFT JOIN not return right value
I am trying to join 3 tables with it foreign keys, but for some reason one of the return value from the query doesn't return correct when I use LEFT JOIN for the 3 tables but I removed the one that ...
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
votes
1
answer
1k
views
Optimize 5 million records SQL query (use join or not?!)
I have a table for products that contains almost 5 million records.
I have a column for the product category (product_category). That it is for now of type INT(11) and it is an index and refers to ...
1
vote
0
answers
203
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 ...
0
votes
0
answers
602
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
2
answers
5k
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
232
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
vote
0
answers
24
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 ...
-1
votes
1
answer
10k
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 ...
0
votes
1
answer
65
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 = ...
0
votes
1
answer
175
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 ...
0
votes
0
answers
526
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 ...
1
vote
1
answer
969
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 ...
0
votes
1
answer
36
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
vote
0
answers
393
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,...