Stack Overflow
Stack Overflow

Questions tagged [non-equi-join]

Non-equi-join is a join using non-equality binary relational operator such as (<, ≤, ≠, >, ≥) . Also known as a Non-equi Theta-Join. Theta join combines tuples from different relations provided they satisfy the theta condition. General form of theta-join can use use all kinds of comparison operators (<, ≤, =, ≠, >, ≥) Both equi-join (join using = operator) and non-equi-join are subsets of general form on Theta-join.

More info about non-equijoin on w3resource.com

46 questions
1
vote
1 answer

What is alternative to performing a left join on GTE or LTE conditions?

I am required to use a specific old version of HIVE that prevents me from joining 2 tables on GTE or LTE conditions. For example what is the equivalent of select * from table1 as t1 left join table2 as t2 on t1.id = t2.id and (t1.date >= t2.date…
lollerskates
  • 964
  • 1
  • 11
  • 28
0
votes
2 answers

How to join "out of range dates/nomatch" to nearest "date range" in non-equi R data.table joins?

I am trying to join two data tables with a data.table non-equi join. However, some of the dates in table 1, do not fall within a date range in table 2. What I want with these "nomatch", is to join them to the nearest date range. Lets says there are…
Nik
  • 39
  • 7
0
votes
1 answer

Contingent data.table sum

I have a data.table representing three variables: Start_time (commencement of a certain procedure) Time (the actual time) Value (some value ascribed to each procedure at a given time) I've formed a dummy set of data to represent this…
Phil
  • 261
  • 1
  • 8
0
votes
1 answer

Replace NA with values from another data frame, nearest in time, by group

I have two dataframes. Both have variables "ID" (a grouping variable), "Timepoint" (class Date), and a value variable "Values". Some example data: df1 <- data.frame( ID = c(1,1,1,1,2,2,3,3), Timepoint = as.Date(c("2019-05-21", "2019-05-23",…
DVNST
  • 3
  • 2
0
votes
1 answer

Rails ActiveRecord Associations for Non-Equi Joins

We have a Rails app that tracks departments asset registrations including subnets and IP addresses. Departments have one to many associations to both Subnets and IPs. In addition to displaying a department's IPs (on other's subnets, @dept_ips…
S Etling
  • 11
  • 2
0
votes
1 answer

Join data by interval from another dataset (non-equi joins) [R]

I am trying to aggregate (FUN = mean) the dataset df1 by intervals of df1$depth from the dataset df2 (df2$minDepth & df2$Depth) and that by group (Station and Transect) in order to join them by the column depth, Station and Transect. The problem is…
C. Guff
  • 418
  • 3
  • 18
0
votes
1 answer

how to make an SQL Join with inequality but just select TOP 1 rows for every mach of the inequality?

I have this tables: table A: id value 1 20 2 15 3 10 table B: id value 1 20 2 14 3 10 I want all the pairs where A.value >= than B.value. But for every comparison in the WHERE condition i just want the first match. In…
davidsalmon
  • 13
  • 3
0
votes
1 answer

R data.table non-equi join based on "not equal"

I would like to merge the data.table to itself based on values where id's are not equal. Here is a small example: library(data.table) #Two tables: dt_1 <- data.table(id = c(1,2,3),x = c(2,3,4)) dt_2 <- copy(dt_1) %>% …
Vitalijs
  • 938
  • 7
  • 18
0
votes
3 answers

R iterating by group and mapping values based on column value

I have the following data frame in R: df <- data.frame(name = c('p1_start','p1_end','p2_start','p2_end','p1_start','p1_end','p2_start','p2_end','p1_start','p1_end','p2_start','p2_end','p1_start','p1_end','p2_start','p2_end'), time =…
CroatiaHR
  • 615
  • 6
  • 24
0
votes
1 answer

SQL for Grouping Teams by Rank

Given two tables named team and award, I need to assign teams to an award based on each team's rank. Here are the two…
Barzee
  • 897
  • 3
  • 15
  • 30
0
votes
0 answers

non equi selfjoin data.table error "vecseq(f__, len__, if (allow.cartesian || notjoin || !anyDuplicated(f__,"

I'm trying to do a non equi self join with data.table on a table that has 2 millions rows and 8 variables. the data look like this: db table : product position_min position_max count_pos A.16 167804 167870 …
Stella
  • 69
  • 1
  • 10
0
votes
0 answers

use non-equi join to define set of data in one data frame that are within a certain range from another data frame in R

Im new to R and I have a very difficult task want to complete. I have two set of data frame. DF1 consists of 810 observations with 4 variables, DF2 consists of 1707 observations with 51 variables. Here is some example of DF1: Chr POS Range_Plus_10…
Sunny Sun
  • 1
0
votes
1 answer

Query on Non Equi joins for Left outer join in Hive

I am trying a left outer join between two tables with the non equi join condition and hive is not supporting it. Adding the condition in where clause result in data loss. Please let me know if any one have a solution for it. following is sample code…
user4836066
  • 51
  • 1
  • 3
  • 7
0
votes
1 answer

What is the difference between inner join and non-equi join?

From definitions i've read on internet, in equi join the join condition is equality (=) while inner join can have other operators such as less than (<) or greater than (>) as well. a non-equi join is a type of join whose join condition uses…
Mohit Saxena
  • 89
  • 1
  • 12
0
votes
0 answers

R summaries when dates in main df fall within ranges from small df

Similar to do.call/lapply approach here, and data.table approach here, but both have the setup of: MainDF with data and startdate/enddate ranges SubDF with a vector of single dates Where the users are looking for summaries of all the MainDF ranges…
dez93_2000
  • 1,730
  • 2
  • 23
  • 34
1 2
3
4