Error in left join suggestion please

SQL++
1

Below is my query where i am doing left join but getting error.Please suggest:
SELECT a.acct_id,p.prof_name as MerchantName
FROM dev_pricing a
LEFT Outer JOIN dev_pricing p On KEYS a.pri_prof.name
where p.dtype=‘mpriprof’ and a.dtype=‘acct’

2

It works. What error.

SELECT a.acct_id, p.prof_name AS MerchantName
FROM default a
LEFT OUTER JOIN default p ON KEYS a.pri_prof.name
WHERE p.dtype="mpriprof" AND a.dtype="acct";

Also you can use ANSI JOIN https://blog.couchbase.com/ansi-join-support-n1ql/

3

i have tried but it doesn’t gives any records.

4

It gives results. Make sure your data is right and have relation ship.

INSERT INTO default VALUES ("k01", {"dtype":"acct", "acct_id":1234, "pri_prof":{"name":"f001"}});
INSERT INTO default VALUES ("k02", {"dtype":"acct", "acct_id":1234, "pri_prof":{"name":"f002"}});
INSERT INTO default VALUES ("f001", {"dtype":"mpriprof", "prof_name":"XYZ Corp"});

SELECT a.acct_id, p.prof_name AS MerchantName
FROM default a
LEFT OUTER JOIN default p ON KEYS a.pri_prof.name
WHERE (p IS MISSING OR p.dtype="mpriprof") AND a.dtype="acct";

"results": [
    {
        "MerchantName": "XYZ Corp",
        "acct_id": 1234
    },
    {
    "acct_id": 1234
}
    ]
5

Thanks vsr.
SELECT a.acct_id,p.prof_name as MerchantName
FROM dev_pricing a
LEFT OUTER JOIN dev_pricing p On Keys (“mpriprof::” || a.pri_prof.name)
where p.dtype=‘mpriprof’ and a.dtype=‘acct’
this query gives result.
But we have total of 23 documents with acct dtype and 77 with mpriprof type and result of inner join and left join is same.

below is sample of doc: mpriprof::102
“prof_key”: “102”,
“prof_name”: “newworld”,
“pri_prof”: “priprof::102”,
“channel”: “channel1”,
“dtype”: “mpriprof”,

document of type acct:
“acct_id”: “12345”,
“pri_prof”: {
“owner”: “U”,
“name”: “102”

6

The predicate eliminates NULL projected rows. You need condition of ( p IS MISSING OR p.dtype=“mpriprof” )

You use following query or ANSI JOIN

SELECT a.acct_id, p.prof_name AS MerchantName
FROM default a
LEFT OUTER JOIN default p ON KEYS a.pri_prof.name
WHERE ( p IS MISSING OR p.dtype="mpriprof" ) AND a.dtype="acct";

OR

   SELECT a.acct_id, p.prof_name AS MerchantName
    FROM default a
    LEFT JOIN default p ON   a.pri_prof.name = META(p).id  AND   p.dtype="mpriprof"  
    WHERE   a.dtype="acct";
7

Second one worked.Thankyou for your help.