I want to run, basically, this, to show brewery info for any that make a Belgian-style Pale Ale:
SELECT name, city, state, country
FROM `beer-sample` brewery
WHERE type = 'brewery'
AND brewery.brewery_id IN (
SELECT beer.brewery_id
FROM `beer-sample` beer
WHERE style = "Belgian-Style Pale Ale"
);
But of course I get “FROM in subquery must use KEYS clause:”. I cannot figure out the right USE KEYS clause. My best guess from what I’ve seen here is:
SELECT name, city, state, country
FROM `beer-sample` brewery
WHERE type = 'brewery'
AND brewery.brewery_id IN (
SELECT RAW beer.brewery_id
FROM `beer-sample` beer
USE KEYS brewery.brewery_id
WHERE style = "Belgian-Style Pale Ale"
);
But that yields zero rows, as does every other permutation I try. What am I missing?
Thanks in advance,
AFC
P.S. I know there are other ways of doing this; I am trying to specifically demonstrate IN subquery conditions
We have added support for subqueries that do not require a USE KEYS clause. This will be released after the GA release. If you need this feature before then, please let us know.
SELECT a.user_id
FROM default a
WHERE doc_type = 'login’
AND EXISTS(
SELECT b._id
FROM default b
USE KEYS a.user_id
WHERE doc_type=‘owner’
);
It contain two different document using same ‘name’ and using one ‘name’ i want to access another…but unfortunately i am not getting any values…What am I missing?
We have added support for subqueries that do not require a USE KEYS clause. This will be released after the GA release.
What is the “GA” ? And is this feature has been released?
I would like to query every documents that has the same FK. For example :
SELECT COUNT(*) as count FROM BUCKET bucket
WHERE `conditions ...`
AND ANY item IN (SELECT subBucket.`fk` FROM BUCKET subBucket) SATISFIES item.`fk` = bucket.`fk` END
This is a simplified query but this is what I would like to do and with the USE KEYS mandatory in subQueries I can’t achieve it.
Before getting on 4.5, could you tell me if their is some way to achieve this kind of behavior in 4.1? Even with some performance issues or with very complex queries? Because we’re still in huge development and not yet ready to engage before project close to release date.
In 4.1, you have to use two queries, unfortunately. Issue the subquery first, and then embed the results in the outer query. You will be better off upgrading if possible.
hii…
I am using one query like below. But giving below error. Query:
SELECT meta(d).id,d.*,FIRST v FOR v IN ud WHEN d.updateDescriptionId = v.id END AS updateDescriptionDetails
FROM ChecklistLibrary AS d
USE KEYS [meta(d).id]
LET ud = ARRAY_FLATTEN((SELECT RAW d2.updateDescriptions FROM ChecklistLibrary d2
USE KEYS [“For_Profit:updateDescription”]),2)
ERROOR:
{
“code”: 3000,
“msg”: “Ambiguous reference to field d.”,