Questions tagged [join]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
38 views

Conditional inner join

I need to get allowed itemTypes for a user based on 2 different tables in a conditional way. If the users userGroup has any entries in the userGroupItemTypes table it should only show items that are ...
0
votes
0answers
19 views

WHERE clause through two tables

I'm currently monitoring log events through an log_events table which references web_pages. Each web page belongs to a domain and has a classifcation. I've used an inner join so far for the query, ...
0
votes
1answer
30 views

I want to join the below mentioned queries into single query

SELECT A.update_date, A.create_user, A.service_id,A.order_id, '10009' AS main_plan_id, 'Prepaid Cash' AS main_plan_name, ' ' AS topping_plan_id, ' ' AS ...
0
votes
1answer
35 views

How to remove NULL values in multidimensional array?

SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid ,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as ...
0
votes
1answer
69 views

(mySQL Aggregate function, JOINS, and GROUP BY) sakila database project

The current pandemic is making it very difficult to contact my teachers for assistance, so I'm hoping I can describe my problem well enough to try getting some help here. The database used is the ...
1
vote
2answers
24 views

How to optimize selection of pairs from one column of the table (self-join)?

I'm using PostgreSQL 9.5.19, DBeaver 6.3.4 I have a table where one row is - user's name, place he attended, time when he was there I need to select all pairs of places where any user was (if user ...
1
vote
2answers
26 views

List all column names for multi-joined table

I have a slightly more complicated join: SELECT person.given_name, person.family_name, person.age, person.sex, person.state, person.zip, person.street, person.residential_number, c1....
1
vote
1answer
56 views

Extract and combine multiple values from a jsonb column

I am looking to extract multiple values from a jsonb column in Postgres, and am running into an issue where some values are coming back null. fiddle Setting up schema: create table jsonb_test (test ...
1
vote
2answers
37 views

SELECT LEFT JOIN but only the record that every matched value is null

Suppose that I have two tables name and location name | location_id 'A' | NULL 'A' | NULL 'B' | NULL 'B' | 2 'C' | 3 'C' | 4 location_id | location_name 1 ...
0
votes
0answers
33 views

MySQL Multiple Left Join returns wrong data

I have an question about MySQL Left Join. When I use only just one Left Join everthings working fine but If I add second Left Join MySQL return wrong results. This is working example, it's querying ...
2
votes
1answer
33 views

How to filter table 2 before joining to table 1 so that the result still includes all of table 1, even if no match in table 2?

The following returns all rows from table 1 where post_type = dlm_download. It groups like values from table 2 by the matching column in table 1 so that each row in table 1 has a maximum of one ...
0
votes
2answers
237 views

Select column name dynamically based on rows from another table in SQL server

I want to select column name from another table row value in single query. CREATE TABLE [dbo].[DynamicFieldsMaster]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](max) NULL, [...
0
votes
0answers
26 views

I'm having trouble generating a list of downloadable items (data from three tables) with a column showing most recent download time for each

I am using the following query to generate a comprehensive download log with data from multiple tables (reduced to three for this example): SELECT wp_download_log.ID ,wp_download_log....
1
vote
1answer
31 views

SQL Conditional Join - remove duplication from Stored Procedure

This stored procedure accepts a search parameter. I want to join on a table valued function if the search parameter is not null. The way I have written it works, but it seems like lots of ...
-1
votes
2answers
45 views

SQL Server - Subquery in joins vs not

My application has huge recordset in various tables (> 1 billion records) and wondering the better approach as I have read multiple contradictory articles. Question is what is better in performance (...
18
votes
3answers
2k views

Strange JOIN ON clause in T-SQL

I am untangling some legacy code and never in all my years have I seen this: Select * FROM GLAccounts INNER JOIN GLCharts ON glaGLChartID = glcGLChartID LEFT JOIN GLCategories ON ...
0
votes
2answers
38 views

What should I index when joining on a table with conditions?

I have a query like this: SELECT "main_table".* FROM "main_table" INNER JOIN "other_table" ON "other_table"."deleted_at" IS NULL AND "other_table"."id" = "main_table"."appointment_id" WHERE "...
0
votes
1answer
18 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
34 views

Inherit INNER JOIN fields in MySql SELECT QUERY

I want to know how I can join 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 join tableA and tableC ...
0
votes
1answer
35 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*...
1
vote
1answer
46 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
27 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
28 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
62 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
24 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
52 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
70 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
20 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
31 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
86 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 ...
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
44 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
28 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
23 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
19 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
64 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
99 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
33 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
32 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
33 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
55 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
162 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
42 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
35 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 ...

1
2
3 4 5
33