Skip to main content

Questions tagged [join]

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

Filter by
Sorted by
Tagged with
2 votes
1 answer
299 views

Select column values linked to exact set of values in another column

Given the below chats_users table in Postgres 9.5, with integer columns chat_id and user_id, I want to find the chat_id associated with an exact set of user_ids, for example: user_id IN (1, 3) => ...
Arrel's user avatar
  • 123
2 votes
1 answer
6k views

How to force a merge join without a hint

Some time ago I used query hints but then realized that most of the time SQL Server is smarter than I. And it's better to build an additional index/reorganize data or the query and get a much better ...
Alex Zhukovskiy's user avatar
2 votes
1 answer
11k views

full outer join not working

I Want to do a full outer join on 2 tables but I get this error, what am I doing wrong? mysql> select * from cajas full outer join almacenes on almacenes.codigo = cajas.almacen; ERROR 1064 (...
Anne Ortiz's user avatar
2 votes
3 answers
3k views

Performance impact of NOT using a foreign key (many dedicated 1:many key-key tables vs non-fk generic key-key table)

We're using a lot of association tables to manage 1:many relationships among a variety of different objects in our system. To illustrate the question, two examples would be: users, events, ...
oucil's user avatar
  • 506
1 vote
1 answer
1k views

Compare two tables with same structure, but differing membership numbers

I have two tables with the same structure and most of the data is the same. I also have a third table that contains ids and names. The ids are connected to the membership numbers discussed below. The ...
Mike Hermary's user avatar
1 vote
1 answer
132 views

How can I Rewrite Update Query to Update Using First Record From Inner Join?

I wrote up a question about trying to do an update with an inner join last week that got a lot of answers that I am currently trying out. Here is the link to the answer I used to get to the point I'm ...
MrWizdl's user avatar
  • 45
1 vote
1 answer
3k views

How to filter multiple many to many relationship based on multiple tags?

I've a scheme of two main tables: problem and tag and a relation (which is a many to many connector) table: problem_tags which excerpt of them is like: Problem table: ----+---------------------------...
Masked Man's user avatar
1 vote
1 answer
149 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 ...
parchambeau's user avatar
1 vote
1 answer
483 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 ...
parchambeau's user avatar
1 vote
2 answers
3k views

Join three tables

I have those three tables SELECT occuId as 'Occupation Id',occuDscr as 'Occupation Description' FROM occupation_field; +---------------+------------------------+ | Occupation Id | Occupation ...
giannis christofakis's user avatar
1 vote
2 answers
839 views

Time based sampling

Is there any way to do sampling on time series data in PostgreSQL (v11) based on a fixed time interval? For example, we are storing 1 record per second for 24 hours in a given table. This means ...
Yash Mochi's user avatar
1 vote
1 answer
2k views

Find unmatched rows between two tables dynamically

I have a function here that is supposed to take two tables as arguments and check if they are the same. create or replace function testing.equal_tables( varchar, varchar) returns void as $...
dizzystar's user avatar
  • 148
1 vote
1 answer
431 views

Speed up complex join with disjunctive condition

I have a table that contains information about orders made by users and their phone numbers. The table looks like this one: table Orders ( OrderID int, UserName varchar(50), PhoneNumber1 ...
leqo's user avatar
  • 113
1 vote
3 answers
10k views

INNER JOIN making COUNT(*) slow

I have a very simple query: SELECT COUNT(*) FROM messages INNER JOIN users ON messages.user_id = users.user_id With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to ...
Darthtong's user avatar
  • 263
1 vote
1 answer
10k views

Join Fact table to SCD Type 2 ... how to write query? (SQL Server)

I can't seem to find this simple fact anywhere. I have a fact table like such in SQL server. fact_picked Emp_Name Date Apples_Picked John May 1 17 100 And a type 2 dimension table ...
user45867's user avatar
  • 1,683
1 vote
1 answer
2k views

Self-join unique pairs

I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...
AbuAbdoh's user avatar
  • 588
1 vote
2 answers
3k views

How to select students that failed at least at all courses that student with id = 1 failed?

I have the following tables: STUDENT (student_id, first_name, last_name, birth_date, year , domain) PROFESSOR (professor_id, first_name, last_name, birth_date, hire_date, title, salary) COURSE (...
Eduard Valentin's user avatar
1 vote
1 answer
3k views

How Can Get Random 2 rows FROM EACH GROUP FROM JOINED TABLE

EXAMPLE Table Dept: dept_id dept_name dept_status --------------------------------------- 1 Sales 1 2 Develpment 1 3 HR 1 ...
rva's user avatar
  • 13
1 vote
1 answer
34 views

Does `USING (column)` provide any memory advantage over an an explicit JOIN condition with ON?

Are there any performance differences between JOIN bar USING (foo_id) JOIN bar ON (foo.foo_id = bar.foo_id) I ask this because at least in theory there is more data to project (a duplicate column). ...
Evan Carroll's user avatar
  • 63.7k
1 vote
3 answers
4k views

How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql

I have legacy tables similar to the following: employee ------------------------------------ | employee_id | name ------------------------------------ | 1 | David | 2 ...
nimeshkiranverma's user avatar
1 vote
1 answer
12k views

How to create the multiple sum query with joins

Please consider following database: Table `invoices`: -- invoice_id Table `amounts`: -- amount_id invoice_id type_id amount I am trying to get the sum for each type from table like this: SELECT ...
guest's user avatar
  • 35
0 votes
1 answer
31 views

Group by unique pairs with no multiplicity

I have a table AreaLocation with AreaID as the PKey and Location whereby there are many locations for each AreaID. Correspondingly there is another table Books with: BookingID | DriverID | BLocation | ...
John Miller's user avatar
0 votes
3 answers
19k views

SQL left join returns null column [closed]

I am trying to do a LEFT JOIN on two tables, where one table has a datetime column, and the other table has a date column. As I am working in SQL Server, I used CONVERT to change the datetime to date. ...
user151202's user avatar
0 votes
1 answer
52 views

Return unique records based on shared column in 2 tables

I'm very new to SQL and currently using SQL Server 2008 R2. I'm fairly certain that I need to use a join but not sure if that's possible. The goal is to return just the customer's email address based ...
Cragmuer's user avatar
0 votes
2 answers
101 views

Joining 3 Tables with Left Join

I have 2 Tables namely Area_ Table and PingData_Table Area Table has the list of the areas along with the Area ID and Area Name AreaId | AreaName | isActive 1 |Jumbo House| 1 2 | DCC ...
psyborg.eth's user avatar
0 votes
2 answers
861 views

Table JOIN and CREATE new table extremely slow

I'm using something like this to create a new table: CREATE TABLE result AS (SELECT calls.*, targ_with_planned_calls.* FROM calls INNER JOIN planned ON calls.first_id = ...
jonmrich's user avatar
  • 101
0 votes
1 answer
466 views

How to use left join with Where clause

I was thinking that I have a very good understanding about my sql and joins but i miserably failed to make this query. I have to make employee leave report, therefore I want all employee list no ...
Muhammad Faizan Khan's user avatar
0 votes
3 answers
2k views

How to SELECT from three different tables using multiple foreign keys

I have 3 tables: languages id | tag | region | language -- ----- ------------- -------- 1 en-US United States English 2 es-ES Spain Spanish ... modules id | ...
Phil Tune's user avatar
  • 103
0 votes
1 answer
1k views

How to calculate the sums of two columns, when that two columns brought by join queries?

I ran two queries as shown bellow; query 1: queried with JobRequiredProducts table and Product table to list the jobId 1 required all product and unit prices SELECT j.jobId, j.productId, ...
Aaby's user avatar
  • 37
-1 votes
1 answer
200 views

What is the correct way to organize tables for a SQL view?

I'm new to data modeling. Currently working at creating a personal contact manager but I need some help with modeling the table relationships for a given view. The final view columns I'm shooting for ...
SeaDude's user avatar
  • 111
-2 votes
2 answers
3k views

11 LEFT JOIN s make query very slow

I have this big query it contain 11 LEFT JOINS . when i run it. it get 12s to return result . is some one can assist me to make speed this query that big help. um new to ...
1323254365's user avatar

1 2
3