Is it possible put Joins on multiple document types in Same bucket

SQL++
1

Hi,

I am using N1QL and facing some issues while joining.

I have different types of documents in same bucket like INVOICE,CUSTOMER,SALES ORDER.

and now I want to join on these type.

So is it possible to do it ?

Thanks
Mukesh

2

Yes, you can do self-joins on the same bucket. Have you looked at the examples with travel-sample?

3

yes you can find the JOIN syntax here;
http://developer.couchbase.com/documentation/server/4.1-dp/n1ql/n1ql-language-reference/from.html

4

Thanks for the reply.

I read the syntax of Joins but little bit confused about Keys.

Here are my documents -

INVOICE DOCUMENTS are -

{
“DOCUMENT_ID”: “INVOICE_1”,
“DOCUMENT_TYPE”: “INVOICE”,
“ID”: 1,
“CUSTOMER_ID”: 2,
“INVOICE_ID”: 1
}

{
“DOCUMENT_ID”: “INVOICE_2”,
“DOCUMENT_TYPE”: “INVOICE”,
“ID”: 2,
“CUSTOMER_ID”: 2,
“INVOICE_ID”: 2
}

{
“DOCUMENT_ID”: “INVOICE_3”,
“DOCUMENT_TYPE”: “INVOICE”,
“ID”: 3,
“CUSTOMER_ID”: 1,
“INVOICE_ID”: 3
}

{
“DOCUMENT_ID”: “INVOICE_4”,
“DOCUMENT_TYPE”: “INVOICE”,
“ID”: 4,
“CUSTOMER_ID”: 1,
“INVOICE_ID”: 4
}

CUSTOMER DOCUMENTS are -

{
“DOCUMENT_ID”: “CUSTOMER_1”,
“DOCUMENT_TYPE”: “CUSTOMER”,
“ID”: 1,
“CUSTOMER_ID”: 1,
“NAME”: “Tulip”
}

{
“DOCUMENT_ID”: “CUSTOMER_2”,
“DOCUMENT_TYPE”: “CUSTOMER”,
“ID”: 2,
“CUSTOMER_ID”: 2,
“NAME”: “Neder”
}

{
“DOCUMENT_ID”: “CUSTOMER_3”,
“DOCUMENT_TYPE”: “CUSTOMER”,
“ID”: 3,
“CUSTOMER_ID”: 3,
“NAME”: “James”
}

{
“DOCUMENT_ID”: “CUSTOMER_4”,
“DOCUMENT_TYPE”: “CUSTOMER”,
“ID”: 4,
“CUSTOMER_ID”: 4,
“NAME”: “Victor”
}

I tried with the following queries but getting zero result.

SELECT * FROM test CUSTOMER JOIN test INVOICE ON KEYS CUSTOMER.CUSTOMER_ID WHERE INVOICE.DOCUMENT_TYPE = “INVOICE” AND CUSTOMER.DOCUMENT_TYPE = “CUSTOMER”;

SELECT * FROM test INVOICE JOIN test CUSTOMER ON KEYS INVOICE.CUSTOMER_ID WHERE INVOICE.DOCUMENT_TYPE = “INVOICE” AND CUSTOMER.DOCUMENT_TYPE = “CUSTOMER”;

SELECT * FROM test CUSTOMER JOIN test INVOICE ON KEYS CUSTOMER.CUSTOMER_ID ;

SELECT * FROM test INVOICE JOIN test CUSTOMER ON KEYS INVOICE.CUSTOMER_ID ;

SELECT * FROM test CUSTOMER JOIN test INVOICE ON KEYS (TO_STRING(CUSTOMER.CUSTOMER_ID)) ;

SELECT * FROM test INVOICE JOIN test CUSTOMER ON KEYS (TO_STRING(INVOICE.CUSTOMER_ID )) ;

Am I doing anything wrong?

Please provide me join query for these two types.

Thanks
Mukesh Raghuwanshi

5

Mukesh,

Try the following:

1 Like
6

Thanks for the reply.

I tried with this.It is also giving zero result.

7

Thanks Geraldss

It works for me using below query-

SELECT * FROM test INVOICE JOIN test CUSTOMER ON KEYS “CUSTOMER_” || TOSTRING(INVOICE.CUSTOMER_ID) WHERE INVOICE.DOCUMENT_TYPE = “INVOICE” AND CUSTOMER.DOCUMENT_TYPE = “CUSTOMER”;

1 Like