Questions tagged [join]

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

Filter by
Sorted by
Tagged with
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 ...

1 2 3 4 5 32