N1ql query Join

SQL++
1

I want join three document.
in first doc I have Id. this ‘Id’ have reference in two other document. Is it possible to join with on Keys.
select a.id,b.aId,c.aId
from test b join test a on keys b.aId
join test c on keys c.aId
is it possible or I have to use ansi join ?
Plz help

2

I believe UNION is what you are looking for:

3

Yes it possible if you have relation ship. You can’t mix and match ANSI JOIN and LOOK UP/INDEX JOIN.
If relation ship is via fields use ANSI JOIN. If relation ship is field to document key you can use LOOK UP JOIN.

join test c on keys c.aId this incorrect. ON KEYS can’t reference current JOIN keyspace (i.e. c, which result in MISSING and no match), It can reference any LEFT keyspaces of the current JOIN (i.e. a or b are allowed).

4
  1. user Doc
    {
    “Id”:“axbcvghhsadidauidsaidao”
    “loginId”: “128@gmail.com”,
    “realm”: “google”,
    “type_”: “user”
    }
    2)policy Doc
    {
    “UserId”:“axbcvghhsadidauidsaidao”
    “policyName”: “comLife”,
    “PolicySource”: “google”,
    “Id”:“ass”
    “type_”: “policy”
    }
    3)Amount Doc
    {
    “UserId”:“axbcvghhsadidauidsaidao”
    “Id”:“123”
    “amountTotal”: “12800”,
    “type_”: “amount”
    }
    Now I want user id,policyName,AmountToal
    please help me with on Keys.
5 
CREATE INDEX ix1 ON default(loginId, Id) WHERE type_ = "user";
CREATE INDEX ix2 ON default(UserId, policyName) WHERE type_ = "policy";
CREATE INDEX ix3 ON default(UserId,amountTotal) WHERE type_ = "amount";

SELECT u.Id, p.policyName, a.amountTotal
FROM default AS u
JOIN default AS p ON KEY p.userId FOR u
JOIN default AS a ON KEY a.userId FOR u
WHERE u.type_ = "user" AND u.loginId = "128@gmail.com" 
          AND p.type_ = "policy" AND a.type_ = "amount";

OR

SELECT u.Id, p.policyName, a.amountTotal
FROM default AS u
JOIN default AS p ON p.userId  = u.Id
JOIN default AS a ON a.userId = u.Id
WHERE u.type_ = "user" AND u.loginId = "128@gmail.com" 
          AND p.type_ = "policy" AND a.type_ = "amount";

If Id is not there in user remove from ix1 and queries use META(u).id