Questions tagged [join]

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

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