Questions tagged [join]

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

Filter by
Sorted by
Tagged with
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, ...
Incnis Mrsi's user avatar
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 ...
Basile Bruneau's user avatar
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. ...
JaySmi's user avatar
  • 21
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 ...
Diya Rawat's user avatar
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 ...
PaulMcF87's user avatar
  • 167
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 ...
KSV97's user avatar
  • 3
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 ...
user1207381's user avatar
-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 (...
omerix's user avatar
  • 91
-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 ...
Shakila Rosli's user avatar
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 ...
Dave's user avatar
  • 197
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 ...
EyeSeeSharp's user avatar
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 ...
Syed M. Sannan's user avatar
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 ...
omerix's user avatar
  • 91
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 ...
Nullified's user avatar
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 ...
Jens's user avatar
  • 1
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....
Luca's user avatar
  • 1
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, ); ...
Joseph Daigle's user avatar
-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 ...
nicole33789's user avatar
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 ...
Edge's user avatar
  • 101
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 ...
BenGik's user avatar
  • 13
-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 ...
Gal Mor's user avatar
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 =...
Fajela Tajkiya's user avatar
-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 ...
Fajela Tajkiya's user avatar
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: ...
Marnix.hoh's user avatar
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 ...
Jitan Gupta's user avatar
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 "...
Level1Coder's user avatar
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 ...
kk_p's user avatar
  • 81
-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 ...
EL-wakil's user avatar
-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 ...
ImSahil's user avatar
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 ...
mrlayance's user avatar
  • 101
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 ...
Phil Tomlinson's user avatar
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 ...
Mohammad's user avatar
-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, ...
Andreass Pramana's user avatar
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 ...
klor's user avatar
  • 157
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 ...
techn9ne's user avatar
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 ...
mideveloper's user avatar
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....
Bluetail's user avatar
  • 103
-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 ...
Ahmed Moayad AlMoghrabi's user avatar
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 ...
kojiro's user avatar
  • 111
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) ...
Kum's user avatar
  • 47
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 ...
mokalovesoulmate's user avatar
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 ...
Googlebot's user avatar
  • 4,521
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 ...
Pragy Agarwal's user avatar
-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 ...
Harshita's user avatar
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 = ...
alex_pc89's user avatar
  • 113
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 ...
Mr. Geologist's user avatar
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 ...
mastaofdisastax's user avatar
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 ...
jayc331's user avatar
  • 13
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 ...
Matteo Tassinari's user avatar
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,...
Harry E.'s user avatar

1 2
3
4 5
40