Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,575
questions
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"."...
