Better way to join the table

Community Support
1

I have following two table and want intersection of these tables

table1:(a:(1 1 3 1);b:(4 5 4 4);d:(7 8 9 6))
table2:(a:(1 2 3);b:(4 5 6);c:(7 8 9))

q)table1
a b d

1 4 7
1 5 8
3 4 9
1 4 6

q)table2
a b c

1 4 7
2 5 8
3 6 9

In sql : select a,b from table1,table2 where table1.a = table2.a and
table1.b = table2.b

Expected Result :

a b

1 4

I can do this using xkey but just wanted to know is there any better
way to do it ?

2

You can maybe get additional ideas from looking at the definition for inter

q)inter

k){x@&x in y}

and use that to derive a general

q)select distinct a,b from table1 where (select a,b from table1) in select distinct a,b from table2

a b

1 4

or this specific case, simply

q)(select distinct a,b from table1) inter select distinct a,b from table2                                                                         

a b

1 4

3

append them,

then select a,b, count i by a, b from table

Then select from result where count > 1

Havent tried but should work…You can also do this with only one
statement using fby I think though haven’t tried with compound keys
either…

-Ivan