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
29 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
39 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 ...
0
votes
1answer
31 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
0answers
19 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
2answers
154 views

changing from inner joins to left joins to include null values?

I need to have this query return all NULL Values for hours for each jobcode. I have tried to change the inner joins to left joins, but the output is the same (only showing the 1 jobcode for one date ...
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*...
0
votes
1answer
17 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 ...
0
votes
1answer
79 views

nontrivial joining a relation with itself

I am a philosopher and am doing my first steps in relational algebra. So here is a puzzle: Let say that we have a relation R with attributes 'R.a' and 'R.b'. Is it possible to make a renaming and ...
0
votes
2answers
192 views

INNER JOIN is extremly slow on some rows and create temp table

The MySQL query SELECT `p`.*, `p.Author`.* FROM `post` AS `p` INNER JOIN `user` AS `p.Author` ON `p`.`userid` = `p.Author`.`userid` WHERE ( ( `p`.`threadid` = 29780 ) AND ( `...
1
vote
2answers
23 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....
0
votes
3answers
140 views

MySQL slow performance and timed out after executing join

I'm not a MySql Server DBA. Two years ago I've created web service that send messages and inserts responses from remote web server in MySQL database (Server version: 5.6.16 - MySQL Community Server). ...
0
votes
1answer
83 views

mysql showing two select statements as one output

Hobby databasist here. In my database, the statement below will output the number of posts per hour which discussed subject foo. select count(postid) from myTable where (sub like ("%...
0
votes
2answers
5k views

How to UPDATE from JOIN with GROUP BY

In a SELECT query of SELECT b.id, MIN(IFNULL(a.views,0)) AS counted FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id HAVING counted>0 How can I turn this query to UPDATE as UPDATE b.number ...
1
vote
1answer
31 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 ...
0
votes
0answers
32 views

How to get the data using where_in (1,3 ) table in mysql?

We have 2 Tables Table 1 is User Table 1: id | name | sub_code ----------------------------- 1 | name1 | 1 2 | name2 | 1,3 3 | name3 | 2,4 Table 2 ...
0
votes
1answer
466 views

Combining GROUP BY and JOIN

This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the ...
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
31 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 ...
0
votes
1answer
92 views

Complex queries in CouchDB across multiple documents

I'm relatively new to CouchDB (more specifically Cloudant if it matters) and I'm having a hard time wrapping my head around something. Assume the following (simplified) document examples: { "docType"...
0
votes
1answer
77 views

Joining a column from another table with InnoDB engine

I'm using MySQL with InnoDB and I want to join the location column from products table into a productStock table. I have this table 'products' mysql> select * from products; +-----------+----------...
0
votes
2answers
69 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, [...
2
votes
1answer
32 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
0answers
24 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
1k views

Bitmap Heapscan is slow

I have 2 tables: mailings and form_responses. The form_responses table has a foreign key with the mailings table, so a mailing has one-to-many form_responses. What I want to do is calculate mailing ...
1
vote
1answer
29 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 ...
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 ...
-1
votes
2answers
43 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 (...
0
votes
1answer
87 views

JOIN type for MS-Access report query

I have 7 tables, 6 of which are in a one-to-one relationship with a table named SLD joined with SLD_ID. SLD has 17 records and I need them all displayed regardless of matches in other tables. Which ...
0
votes
2answers
37 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
2answers
31 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 ...
5
votes
2answers
8k views

Casting an array of texts to an array of UUIDs

How can I cast an array of texts into an array of UUIDs? I need to do a join between two tables: users and projects. The users table has an array field named project_ids containing the project IDs ...
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
229 views

MySQL multiple table joins and multiple data levels - best practice

I have multiple tables i wish to join up to produce one big query with specific columns summed. I was having a problem getting the correct summed columns in the final query (i believe this was ...
0
votes
1answer
2k views

Join JSONB column to normal PK column in Postgres

I am trying to join a normal table to a table with a jsonb field via a property on that jsonb field in Postgres. Example: Normal table: create table Res ( id uuid default uuid_generate_v4() ...
0
votes
1answer
39 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
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
173 views

MySQL get values from the other table by using inner joins

In mysql I have 3 tables called as ps_product_lang ps_order_detail and ps_image here are the tables sample data === ps_product_lang === CREATE TABLE IF NOT EXISTS `ps_product_lang` ( `...
0
votes
1answer
146 views

How to access Multiple DB2 Relational Databases In A Single Query

I need a solution for below: There are Two DB2 instances with two authorization ID on the same server and can be access using same user User1. Example: To access Inst1DB, I use the following ...
0
votes
1answer
25 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
92 views

Slow query with multiple group by

I have a query to get all the comments from threads where a given user has commented and not yet seen the comment. So if user 1 commented to a topic, then user 2 commented after his comment, then user ...
12
votes
4answers
64k views

How to get a group where the count is zero?

I'll try to make a graph from the data from my SQL server database. I'll have all streets with the count of the users who are living in this street even the count is zero. For this I've tried this ...
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 ...
1
vote
1answer
184 views

Delete query does not end

I have a following table with 1 million records : CREATE TABLE `activity` ( `id` int(9) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `subscription_id` int(10) unsigned NOT NULL, `...
0
votes
1answer
248 views

Poor performance on MySQL 5.7 on joined tables ( scanning wrong table ?)

I have a joined table (News, Publishers) with indexing. The query is working fine on MySQL 5.5. After I upgrading one of the server to MySQL 5.7, I start noticing high load, high CPU, and slow query. ...
0
votes
1answer
204 views

Oracle DB union two select statements which return multiple rows

I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more than ...
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
1answer
258 views

How to split a multivalued column, do a lookup and update another column?

This is a part of my previous question. How can I achieve the following for all the records in my table using a single query? SET @TeamMembers = TeamMembers from tblProjects where ID = '125' ...
0
votes
1answer
20 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
50 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"."...

1
2 3 4 5
32