Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,560
questions
0
votes
1answer
16 views
Detect with Left Join duplicates entries and return the first value, also return sum of duplicates entries
¡Hi!, i have a case with two tables than can have a lot of matches entries and the time of fetching increases a lot.
This tables can be
Table A: Employees
-------------------------
| Name | ID | ...
0
votes
2answers
22 views
Inherit INNER JOIN fields in MySql SELECT QUERY
I want to know how can i inherit one field of an INNER JOIN table in a SELECT QUERY.
Here's what i got so far:
In this query my result would be NameA, NameB and idC, but i want to inherit tableA and ...
0
votes
0answers
25 views
POSTGRESQL JOIN not working
Running the following...
SELECT
a.allocation_date, a.trade_account, a.trade_broker, a.account,
a.custodian, a.book, a.symbol, a.side,
SUM(a.quantity)::integer,
SUM(a.quantity*...
0
votes
1answer
32 views
MySQL: Does the order of the ON condition of a LEFT JOIN matter?
I have two tables, employees and unionMembers. The people in unionMembers are a small subset of the people in employees. I want to do a SELECT query using a LEFT JOIN like so:
SELECT wage, ...
0
votes
1answer
24 views
how to show combined results of two tables?
I want to show the result as combination of two.
Table 1
SELECT au.invoice`.`InvoiceID`, au.invoice`.`UserID`
FROM au.`invoice`
WHERE au.`invoice`.`InvoiceID` > 11126
-- total results 757
...
0
votes
1answer
23 views
Select rows from two joined table that not in a third table
I have 3 table A,B,C,D with the same columns and I'm trying to retrieve the
user_id that are in table a and b but not c.
For instance:
Table A
user_id closed acct
10102345 ...
0
votes
1answer
60 views
Slow query when matching columns from second table in JOIN with GROUP BY
Update: so, as mentioned in the comments thread, it seems the ORDER BY might be the most direct cause of slow execution. So, the question should be: how can this query use ORDER BY score and execute ...
0
votes
1answer
17 views
Postgresql - join most recent row
I have a query:
SELECT
Author.id,
Author.name,
Author.surname,
Author.photoURL as photoURL,
Movie.title as lastMovieTitle,
Movie.publicationDate as lastMovieDate,
Movie....
1
vote
1answer
42 views
Postgresql group by for many columns
I have such query:
SELECT "Movie"."title",
"Movie"."id",
"Movie"."publicationDate",
"Movie"."authorID",
"Author"."photoURL" as "authorPhoto",
concat("Author"."...
0
votes
1answer
55 views
Wrong results on queries with join for some MariaDB versions
I found that on some versions of MariaDB server some simple queries return wrong results for certain data.
I have 2 tables:
CREATE TABLE `tab_parent` (
`id` bigint(12) unsigned NOT NULL ...
0
votes
0answers
15 views
Filtering on JOIN condition before executing GROUP BY
Have the following function:
CREATE FUNCTION Game.operator_quotes(i_operator_name TEXT, i_locale_id SMALLINT)
RETURNS TABLE (
name TEXT,
rarity SMALLINT,
quotes JSONB
)...
-1
votes
1answer
29 views
MySql LEFT JOIN returns wrong first in PHP page, but correct results in MYSQL command line
We added two columns to our main MySQL table (named objects), a column for quantity and a column for id of the units. If the quantity and/or units are unknown, those columns are set to NULL.
We ...
-2
votes
1answer
77 views
Unexpected result left outer join [closed]
I have 2 tables with a key for the Column_A, Column_B and Column_C combination.
I want to left join the rows of Table_A with the rows of table_B. See de query
beneath. I wanted the result of the ...
-2
votes
0answers
46 views
Why does using table alias in JOIN clause causes syntax error, when it didn't do so before?
How would you explain peculiarity where SQL used to work before and now does not:
SELECT *
FROM employee.user staff
LEFT JOIN support.groups groups ON staff.group_id = groups.group_id
Gives
...
0
votes
4answers
56 views
How to visualize joins between inner queries to their respective outer queries?
I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query.
Here's an example:
SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
WHERE ...
-1
votes
1answer
21 views
Update table column only if the ids don't exist in another table
I have two tables that are in play here. A users table and a users_roles table. I need to update the users status column to 0 for almost all users except for a few, we can target these users by uid ...
2
votes
1answer
42 views
Create new table with a sum where row from table 2 matches field in table 1 and aggregate on another field
I am fairly new to SQL so apologies if my terminology is incorrect. I am looking to summarise a number of measurable's from "Table 2" but in the example below I have only included 1 for ease. I would ...
0
votes
2answers
27 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
57 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 ...
0
votes
1answer
22 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
39 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
18 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
61 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
41 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
75 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
33 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
30 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
31 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
16 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
70 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
32 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
31 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
34 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
17 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
26 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
29 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
30 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
65 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
328 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
43 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
67 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
42 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
23 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
18 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
59 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 ...
