conditional join

Community Support
1

Hi,

how can I perform a conditional join in q ?

I am looking for the q equivalent of the sql:

   select * from t1, t2 where t1.date < t2.date and t1.number1 < t2.number1 and t1.number2 < t2.number2

The only idea I have is to perform a full join and then filter the result table.

However, that might blow up memory for large tables, even if the desired final result set is small.

Thanks,

Frank

 

2

Hi - a reproducible numerical example is much more likely to get responses.

Provide one if you can!

Terry

3

Hi Frank

I am not too sure what exactly you meant, I think if you could provide an numbercial example, that would be extremely helpful.

I also tried following example, hope this is helpful.

For 2 tables, t1 and t2

t1:flip datenumber1`number2!(2018.04.19 2018.03.04 2018.05.16 2018.05.23 2018.04.12 2018.02.22 2018.05.07 2018.04.03 2018.04.23 2018.04.09;10?50.5;10?60.5)

t2:flip datenumber1`number2!(2018.06.23 2018.04.15 2018.05.03 2018.05.31 2018.06.27 2018.07.08 2018.05.29 2018.04.24 2018.05.13 2018.06.09;10?50.5;10?60.5)

you can do the following

(select from t1 where number1 < exec number1 from t2),(select from t2 where number1 > exec number1 from t1)

Thank you

Kino

4

Hi guys:

thanks for replying.

Here is an mock-up of what I am currently doing:

    t1:([id:10#“x”]a_1:(til 10 ); b_1:(til 10 ));

    t2:([id:10#“x”]a_2:(til 10 ); b_2:(til 10 ));

    t3: ej[`id; t1; t2];

    t4:select from t3 where a_1<a_2, (b_1*2)=b_2;

t1 and t2 are tables with 10 rows each.

After joining, t3 has 100 rows from which I ultimately pick out 4 rows with my select statement to achieve my “conditional join”.

This obviously does not work when t1 & t2 have billions of rows.
Is there a way to apply the condition on-the-fly while joining ?

Frank

5

Hi Frank,

yes right, numerical examples will give you right answers.

please post sample data and requirements.

maybe try:

n:exec count date from t1

m:exec count date from t2

(select from t1 where date < n?1#asc exec date from t2),(select from t2 where date > m?1#desc exec date from t1)

Thanks

Neetha

6

t1 and t2 are not “valid,” as the key of each table are all duplicates.

7

Flying, what do you mean with not valid ?
Both tables are happy and alive with duplicate keys.

There is not enforcement of keys uniqueness. 

8

Invalid as in semantically wrong. q, indeed, doesn’t enforce uniqueness in dictionary’s key, but that doesn’t make such dictionary semantically invalid. An ej on t1 and t2 above equates a full cross join of both tables.

If you are indeed talking about ej on two “valid” tables (i.e., with unique ids in the respective tables), a quick select from t1 lj t2 where … will work better, IMHO.