Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,738
questions
0
votes
1answer
28 views
Postgres foreign data wrappers weird join behavior
So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins.
Keep in mind the following snippets are ...
1
vote
3answers
117 views
Pre-filtering on right table in a LEFT JOIN
I have the following schema/query where I basically need to filter parent table by some criteria and then collect aggregated data from its child table(s):
create table parent (id integer primary key, ...
0
votes
1answer
38 views
Create result set with all records from LEFT table, and from the RIGHT table only bring records that do no exist in left already
I'm having a bit of trouble wrapping my head around a way to make this JOIN work.
Postgres 11.5
I have two tables:
Table1:
customer_id
date
(various other fields)
Table2:
customer_id
date
(...
0
votes
1answer
3k views
ERROR: subquery must return only one column
I have two tables one is Category and other one is Product.
Table Description are
Category
name
description
parent_id (self referencing key) (can be upto 3 levels)
Product
name
description
type
...
6
votes
5answers
1k views
What is the point of outer in a join?
I have made a script which harvests mac addresses from switch ports and puts them in a database. I insert it into an import table to be copied over later to the real table. All fine. Now I am writing ...
0
votes
1answer
211 views
JOIN/Union Two tables, removing NULL where possible (similar to pandas concatenate)
I am wondering how to perform an operation that I guess is most akin to a concatenate if you're familiar with pandas.
Basically, what I want to do seems like a UNION where records are "collapsed" in ...
0
votes
1answer
19 views
How to get latest timestamped result from time series in a join table [duplicate]
I have 3 tables
node
----
id
ip
metric
---
id
key (str)
node_id (FK on node)
his_metric
----
id
metric_id (FK on metric)
value (float)
datetime (datetime)
Question
How to write a query that will ...
0
votes
3answers
39 views
How do you join three or more records from the same table?
I have table with a lot of data in it, and we would like a way to essentially flag certain documents to indicate that they are related to (x) other documents:
TABLE A
-----------
id
...
1
vote
0answers
13 views
Calculating forecast data in parallel
I'd like to ask about how to calculate forecast data in parallel in a relational DB like PostgreSQL, which seems like a very typical problem when doing forecasts. Let's say we're trying to get the ...
0
votes
1answer
17 views
Need to Bring Back most Recent Departure Date from Sales table for Customers in Recipient table
Looking at 3 Tables - Recipient, HouseholdScores, Sales.
I need to bring back the most Recent tsDepart (departure date) from sales for the iCustomerId in the Recipient Table. There is no Depart date ...
1
vote
1answer
40 views
How do I optimize my transaction level running balances cartesian join?
This is a continuation of an question found here:
Generate multiple running totals with GROUP BY day
This is a continuation of a previous question, where table definition and sample data can be ...
1
vote
1answer
24 views
Return previous running total when value is null in a time series
This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there).
All of this is being done on a PostgreSQL ...
1
vote
1answer
78 views
Generate multiple running totals with GROUP BY day
I have a set of transactions of stock purchases by users and I want to keep track of a running balance of each stock as the year progresses. I am using a windowing function to track the running ...
0
votes
0answers
31 views
Why does UNION fail here?
I have a problem that I can't seem to figure out how to fix.
What I want to do is simple, select data and append a row of sums as the last row. But I keep getting the error
An error occurred in ...
-1
votes
1answer
25 views
SQL joining three tables and displaying column with values zero count
Im trying to display all the city names and their counts of events where the event price = 0
create table event
(eventID decimal(12) not null primary key,
AddressID decimal(12) not null,
eventName ...
1
vote
1answer
26 views
Show all rows even when count is null
I want to see all the rows from one table and then do a count on the second table to see how many match, but want to see it all even if there are 0 matches in the second table.
My query:
SELECT
a....
0
votes
0answers
35 views
Optimise the selection of two distinct column pairs from the same table
I need help with rewriting or editing the query/logic to improve it's performance. SQL Server 2014
I have the table structure specified below. I want to select two column pairs:
(SourceID + ...
0
votes
1answer
14 views
Ordering a parent table by SUM() of colums of two of its child tables
I know that title might sound confusing, but i will try to explain my problem as much as i can.
I have four tables:
companies{
id,
name,
address
}
stores{
id,
name,
address,
company_id
}
invoices{
...
0
votes
0answers
18 views
Matching columns within the same table in SQLite
I have a table of Employees which contain, among other things, the employeeId, the lastName and the ReportsTo column, that refers to the manager ID to which the employee has to report. That manager is ...
0
votes
1answer
46 views
Get data from different tables indexed by same ID
Database: postgresql 12. I have a table people:
person_id | name
----------+-----
1 |John
2 |Paul
3 |Jim
...
Each person can have zero or more car insurance contracts, so I ...
0
votes
1answer
141 views
LEFT JOIN LATERAL conditions and JSONB
I have a JSONB column (jb_column) that may have a key (let's call it schrodinger) or not. When the schrodinger key is present, it's value can either be null::JSONB or a regular JSONB object.
First, a ...
1
vote
1answer
82 views
MySQL - view select rooms not in booking range
I'm using a select query within a view to find when a room is available, a booking is identified with a reference and a start/end date.
view:
USE CATTERY2;
CREATE VIEW RoomAvalability
AS
SELECT ...
0
votes
1answer
43 views
Postgresql group by and count() problem, missing row with 0 result
I have a problem with group by and count().
I need a result table looks like below.
Tried I guess all the variations of JOIN.
There are two types of wrong result:
- first when the row "3 C 0" is ...
0
votes
1answer
24 views
Select value in Left Join ordered by Date
I am trying to LEFT JOIN a value to sort by the most recent date, but for some reason it is only returning a result for the last returned result.
These are my table layouts
cs_users
+---------+-----...
0
votes
1answer
18 views
How to rewrite this correlated query as a JOIN?
I'm just getting into SQL and am encountering some difficulty wrapping my head around correlated queries. I'm still trying to master it but am wondering what would be the right syntax for rewriting ...
0
votes
1answer
35 views
I dont know where to begin: mysql select with inner selects on iterations too slow with bigger tables ( 4 tables on 2 databases)
Kontext is easy:
A) A [discussion-thread.db1] has many [Posts.db1]
B) each Post has an [author.db2] and 1 to 10 [attachments.db1]
Additional Information:
discussion, posting & attachment's-...
0
votes
1answer
30 views
How can I rearrange these two queries to get the second column from inside the IN()
How can I get the random.id column from inside this first select
select id, name from random where id IN(1,2,3,4);
+----+-----------+
| id | name |
+----+-----------+
| 1 | Frank |
| 2 | ...
0
votes
3answers
51 views
Join without indexing the entire table
I have a query that pulls the 3 most recently commented posts on our forum. The issue is is that COUNT(forum_posts.id) as posts, is indexing the entire database of 600,000 posts and the query takes ...
0
votes
1answer
47 views
Can I Make This Query Faster?
I have this SQL query:
SELECT parent_category FROM category
JOIN
(
SELECT category_id FROM venue
JOIN
(
SELECT venue_id FROM city_venues
WHERE city_id = 3
) AS venues
...
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
23 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
31 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
726 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
204 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
32 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
29 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....
2
votes
1answer
566 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
39 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
81 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
36 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
4k 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
29 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
70 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
52 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 ...
1
vote
2answers
45 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
43 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
76 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
43 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
560 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, ...
