CODEWITHSUNDEEP
sql-serverkdb+
michaelg
michaelg

Reputation: 243

Sql code into kdb code

I am taking a query in SQL Server and trying to write it in KDB. I have done this before without issue but this time the kdb query seems to be returning a different amount of rows than SQL when it should return the same. My SQL code is:

SELECT
 *
FROM tbl_Master
INNER JOIN map_2012
ON tbl_Master.RM_ID = map_2012.RMID
LEFT JOIN src_CQ
ON map_2012.INST = src_CQ.INST

My kdb code is

Y:map_2012 ij `RMID xkey select RMID:RM_ID, RG_ID, RM_Name from 
tbl__Master
Y:src_CQ lj `INST xkey Y

It is simple code but I can not figure out why the returned rows are so much different

Upvotes: 0

Views: 268

Answers (1)

Anton Dovzhenko
Anton Dovzhenko

Reputation: 2569

In SQL query you left join src_CQ to map_2012. To do the same in KDB map_2012 has to be on the left and src_CQ on the right:

Y:Y lj `INST xkey src_CQ

Also note, that in case map_2012 has duplicate values in RMID column or src_CQ has duplicate values in INST column, keying of table in KDB will remove rows with the duplicates. Under these circumstances another approach is required, I believe equi-join eq will help in this case.

Upvotes: 1

Related Questions