Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,544
questions
0
votes
0answers
9 views
Selecting rows with a foreign key OR a join table relationship
I have tables like this
# Things
id
user_id
# ThingUser
id
thing_id
user_id
I want to select all Things where user_id=123 OR it is related to user 123 through ThingUser.
I think maybe I'm close ...
0
votes
0answers
34 views
Getting View of One to Many where Column Names are repeated with Indexed field
I have 4 tables joined with an inner join.
2 of these joined tables are 1 to Many relationships. Consider the following example return where t1 is table 1, t2 is table 2 and so forth. table 1 has a 1 ...
-1
votes
0answers
20 views
CASE WHEN SET vs SET CASE WHEN
I have these normalized tables:
modules
id | module | type
-- ------ ----
1 foo VARCHAR
2 bar TEXT
3 baz VARCHAR
content
id | module_id | varchar | text
-- --------- ...
0
votes
0answers
16 views
MySQL query help
I need a query that returns the number of liked messages and orders them by the number of likes (from most to least).
I have tried:
SELECT users.user_id,
messages.author,
messages....
0
votes
1answer
35 views
How to show multiple record if has top 1
Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-...
0
votes
0answers
14 views
Is there a way to reduce the number of joins?
I have an ETL job that looks like the following:
INSERT INTO awsdatacatalog.db.table0
(SELECT
field0 AS other_name0
fieldN AS other_nameN
-- PARTITION KEYS
, 'partition0' ...
1
vote
2answers
59 views
How do I join getting one row from the left table, no matter how many matches i get from the right table?
I have two tables - one is a data table and the other is a mapping table. I want to join them together, but only preserve the data from the left table. However, it is possible that the mapping table ...
0
votes
0answers
39 views
query start, due, complete dates for first and last time phases from phase tracking table
linked excel file
I have a phase tracking table which consists of the fields record id, phase name, record order number etc:
RECORD_ID,phase,RECORD_ORDER,SUBMITTED_DATE,DUE_DATE,COMPLETED_DATE
1657,...
0
votes
1answer
70 views
MSSQL Join between big and empty tables
I've got an issue with estimated cost and join predicate :
SELECT c.Id FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT JOIN Table_C c ON c.Id_B = b.Id
WHERE a.Id = 2500
OPTION (RECOMPILE)
...
0
votes
1answer
30 views
Filter the query result
I have one query by which I am getting duplicate ComapnyID and I want to filter that and remove duplicate ComapnyID but don't want change the order by report.
table 1 reports
Table 2 Company
Here ...
-2
votes
2answers
31 views
Limit join to one per row
Say I have many players, each with a numerical rating, stored in a ratings table that keeps track of both current and historical ratings (i.e. it has player_id, rating, and date columns).
How would I ...
-2
votes
2answers
29 views
Inner Join on 3 tables SQL server 2017 [duplicate]
I have three tables
1. Ordertable
2. Bill
3. Customers
In Ordertable I have following columns
(*) represents Primary key here
| Customer ID*c| Customer Name | Address | Contact Number | ...
0
votes
1answer
29 views
Getting one record per Emp_ID
I wrote a query whereby I joined multiple tables to get one row per employee id and the relevant information for each employee job code, job code etc
SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A....
0
votes
0answers
12 views
How to $lookup embedded documents in MongoDB aggregation pipeline?
I have 3 collections each related to the other. Let's assume that first collection is University:
{
_id: ObjectId(),
name: '',
description: '',
creation_date: '',
address: ''
}
And Teacher ...
1
vote
1answer
24 views
Assistance with MySQL query
I have following tables
Tags
id | tag_name | slug |
1 | tag1 |tag1
2 | tag1 |tag1
products
id | proudct_name
1|product1
2|product2
product_tags
id | product_id | tag_id
1|1||1
2|1|2
3|2|1
i ...
-1
votes
2answers
58 views
How to select only one row for each distinct value
I have a pretty long query which includes subqueries and joins that I need to run for a report. It runs without error, but the results show 11 rows for each value, when I need it to display 1 row per ...
0
votes
1answer
30 views
bridge table joined table have both the keys equal to both the keys
SELECT DISTINCT i0.id,
i0.name
FROM main_table AS i0
RIGHT OUTER JOIN "bridge_table" AS i3 ON i3."main_table_id" = i0."id"
RIGHT OUTER JOIN "other_table" AS r4 ON i3."other_table_id" ...
0
votes
1answer
30 views
Joining two select statements
Im using MySql 8.0 and I've been exercising a lot of quries but this one I cant seem to figure it out. I need to join somehow two select statements that I've written and they display the correct ...
0
votes
1answer
33 views
Update table with values from another table runs really slow
I have a PostgreSQL table (tab_A) with about 32 million records, and I have a second PostgreSQL table (tab_B) with about 4000 records. tab_B contains 3 fields whose values I intend to pass on to tab_A ...
-1
votes
1answer
14 views
Arabic language search in codeigniter error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
Arabic language search in codeigniter error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
In English language search is working
JOIN Query to use to ...
0
votes
1answer
24 views
Can’t Get My JOINs Right for this Query
With the SQL Fiddle at http://sqlfiddle.com/#!18/fce3e/1, I’m trying to get the following results, but can’t wrap my head around it. I don’t want to specify a specific category or personId values.
...
0
votes
1answer
28 views
Efficient method of inserting a column into a table based on another table
Consider the following tables
CREATE TABLE Main (
id INT PRIMARY KEY,
state VARCHAR(255) DEFAULT NULL
)
CREATE TABLE ColorTable (
color VARCHAR(255),
state VARCHAR(255)
)
INSERT ...
-1
votes
2answers
29 views
Theoretical - Given 2 Tables, is it possible to calculate the size of the resulting dataset after a join?
This may not be the most appropriate exchange for this, so my apologies in advance.
I was posed this question during an interview prep session for an SQL developer role and was not given the ...
0
votes
1answer
61 views
Multiply several discrete values in multiple columns and then sum
I need to multiply values from two tables and sum results. The database is either Postgres or MSSQL (long story).
Table Bought_Products
ID | Name | Customer_ID | Product_ID | Foo | Bar | Blarg
1 | ...
4
votes
1answer
320 views
Optimize a slow nested loops join
I have this query running around 15 seconds on my system. The query plan is here. I believe the main problem is around here, where the optimizer chooses nested loops and is mistaken with cardinality:
...
1
vote
2answers
40 views
Alternative to slow [LEFT JOIN + OR] without UNION?
I have a reasonably simple query that returns all purchases for a given user, searching by his government issued id. The document could be in two different tables.
The query using only left joins ...
0
votes
0answers
64 views
Proof Full Outer Join associative and Commutative
I'm trying to prove to myself that the order of full outer joins doesn't matter, but, in an abstract sense, I'm coming up with nothing.
How would one go about proving that the order of a set of FULL ...
0
votes
3answers
37 views
How to SELECT from three different tables using multiple foreign keys
I have 3 tables:
languages
id | tag | region | language
-- ----- ------------- --------
1 en-US United States English
2 es-ES Spain Spanish
...
modules
id | ...
0
votes
0answers
24 views
1 table with 2 same foreign keys to 3rd table with Primary Key
First time building a database in Access. I've been searching, but I did not find something to solve my question, so I finally decided to ask.
It may be a really stupid question but what is the best ...
1
vote
1answer
20 views
Joining a table on an id but need to copy this data to other ids
I have an issue with a postgres database that I am not sure how to handle.
These are the tables (simplified obviously)
Users
+--+---------+
|id|user info|
+--+---------+
|10|{data} |
|11|{data} |...
-1
votes
1answer
20 views
compare sum of one table with a field on another table
I have two tables invoice and allocation. The common id is
Invoice.invoice_id = Allocation.Invoice_RK
The allocation table may have multiple rows with one invoice_id and amount column. The invoice ...
0
votes
1answer
17 views
SQL: Select all items and add info from left join where condition is met
Background
I am creating a news site. Table NEWS has all news with unique ID news_uid.
TABLE NEWS
--------------
news_uid | ...
If a user has read a certain news, that info gets stored in table ...
-1
votes
1answer
58 views
Can't get this query to work properly
I've been trying to get the following query working for days but I can't seem to solve my issue. I'm using the MySQL version 5.7.9
I have the following database:
CREATE TABLE user (
id INT NOT ...
1
vote
1answer
124 views
How can Inner join be optimize? [closed]
I have a table where there are in some cases twin records.
In the table, I have the following columns:
id (identity column)
action_id
session_id
row_id
timestamp
...and more records.
I need to ...
3
votes
1answer
65 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 ...
0
votes
1answer
46 views
How to query Last inserted row for each group in oracle 10
I have two tables about users where I want to join two tables and show the latest remarks of each user
bellow is table
I want to query the latest remarks for each user (1 remark per user)
bellow ...
2
votes
1answer
69 views
Increasing join performance on very large table
I have the following query in Postgres database
SELECT
(rel."external-institute" || '#' || rel."external-institute-code")::varchar(50)
FROM public."td_documents" d
JOIN ...
3
votes
0answers
40 views
MySQL uses INDEX for optimizing ORDER BY with GROUP BY except when you add a JOIN
Question
Why can't Query #2 use the same (car_trims.horsepower_peak) index to optimize the sorting of the rows as Query #1? The only difference between the two queries is the addition of the JOIN in ...
0
votes
1answer
123 views
MySQL JOIN query takes too much time to get the result
I have a MySQL query as follows which JOINs 8 tables. When I use 3 tables to get the data the result is getting within 10 seconds. But whenever I add one more table the fetching time goes upto 1 ...
-4
votes
1answer
22 views
MySQL returns more than one row with AND operator match
I have two tables 1:n relationship, customer to orders the schema is in the http://sqlfiddle.com/#!9/a6842e/1 I have created.
The key in customer is composite id and type. I am trying to get the ...
0
votes
0answers
23 views
Is it possible to join on column placeholders?
I have a query below in which i was wondering whether it is possible to join on my placeholders and if so are my joins in my CTE's correct?
WITH
DCM AS (SELECT
date,
placement as creative,
SUM(...
0
votes
1answer
19 views
group by with joins
So I have 2 tables with identical structures that represent data from 2 vendors.....I would have set this up in one table but I lost the argument with the data guy...anyway....keep getting an error ...
2
votes
1answer
26 views
Select IDS where all values can't be found in another table
Hey I'm pretty new to SQL and I've come up with a problem that I haven't been able to solve
I'm trying to make a query to bring up ID's that have ALL null values and are located in another table.
DB ...
8
votes
1answer
551 views
Replace long GROUP BY list with a subquery
This is a repost of my question on Stack Overflow. They suggested to ask it here:
I found an online article from 2005, where the author claims, that many devs use GROUP BY wrong, and that you should ...
0
votes
0answers
17 views
How to fetch different record when nothing matches in where conditions in the same query?
I am using ORACLE 12C database.
There are two components - Agreement and Case.
Agreement related information stores on Agreement_line and Object Table. Common column between these tables are ...
1
vote
0answers
15 views
Understanding PostgreSQL Execution Plans
I'm trying to understand the motivation of PostgreSQL 12 query planner here.
Database Structure
CREATE TABLE subjects
(
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
);
CREATE TABLE ...
6
votes
2answers
154 views
Why selecting rowversion using outer join never returns null
Using SQL Server 2014.
Given table with rowversion column, other table to join with and a select like this:
CREATE TABLE dbo.FooTable(
[Id] [int] IDENTITY(1,1) NOT NULL,
[RowVersion] [...
2
votes
0answers
30 views
Why can't outer join simplification happen for embedding outer join?
The MySQL documentation here suggests the following:
Sometimes the optimizer succeeds in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:...
0
votes
1answer
62 views
How to improve performance for table JOINS involving millions of rows
I am dealing with a performance issue for a very simple structure involving simple queries.
Consider two tables called TBL01 and TBL02. TBL01 has a master-detail relationship with TBL02. The primary ...
0
votes
0answers
22 views
How does MySQL optimizer compute join cost when indexed?
So, The standard formula in theory is
C(R1.R2) = C(R1) + T(R1)*C(R2) where,
C(R1.R2) - Cost of joining two relations (tables) R1 and R2
C(R1) - Cost of reading relation R1
C(R2) - ...
