People who code: we want your input. Take the Survey

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
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, ...

1 2 3
4
5
35