N1ql join query issue

SQL++
1

Hi Guys,

I am learning N1QL JOIN and I am finding it difficult to get it right even after reading a lot of examples and documentation. I have the below 2 documents in the same bucket and I want to join them to display the employee details along with its salary details. Can you please suggest the correct query ?

“id”: “SAL001”
{
“type”: “salary”,
“emp_no”: “101”,
“fixed_salary”: 10000,
“HRA”: 1000,
“travel_allowance”: 500,
“frequency”: “monthly”
}

“id”: “EMP001”
{
“firstname”: “John”,
“lastname”: “Smith”,
“emp_no”: “101”,
“type”: “employee”
}

I tried this but giving syntax error.
select P.* , Q.* from Products P INNER JOIN Products Q ON KEYS P.emp_no=Q.emp_no and P.emp_no=101

2

Pre 5.50 N1QL supports LOOK-UP, Index Joins. You can join only between document field to document key.

In 5.50 ANSI JOINS are supported. You can Join between document fields.

Based on your query you need to use 5.50 which is released today.

SELECT P.* , Q.*
FROM  Products P 
INNER JOIN Products Q 
ON P.emp_no=Q.emp_no
WHERE  P.emp_no=101;
3

Thanks a lot,

I upgraded CB to 5.50 and I am able to join the documents.