JOIN or SUBQUERY Question

SQL++
1

I have in the same bucket

“challengeUser”: {
“counter”: “2”,
“challenge_id”: “10487920”, <-- This the challenge.counter unique Id for challenge
"doc_type": “challengeUser”,
“user_name”: “georgeK”
}

“challenge”: {
“counter”: “10487920”,
“challenge_id”: “A00-870”, <-- this is challenge code not related to challengeUser.challenge_id
"challenge_number": 106,
“doc_type”: “challenge”,
“status”: “CANCELLED”
}

I like to join or sub -query to get all challenges a given a user_name = georgeK is in

I have tried everything I think it can not be done as the

SELECT * FROM challenge c WHERE c.doctype = “challenge” AND c.counter IN (SELECT cu.challenge_id FROM challenge cu USE KEYS c.counter WHERE cu.doctype = “challengeUser”);

returns empty ARRAY

Any help is welcome

2

I don’t fully understand the USE KEYS Am i missing something ?

3

You relationship is on 2 attributes and N1QL does not support this just yet. You need the relationships set up in a attribute to key fashion.

regarding USE KEYS, it simply restrict the keyspace in the FROM clause to the list of keys specified by the USE KEYS clause. So in your query USE KEYS c.counter restricts cu to just the list of doc IDs that qualify to c.counter.

it was not obvious reading the text but, What is the relationship between challenge and challengeUser? 1-to-many or many-to-1 or many-to-many?
thanks
-cihan

1 Like
4

Thanks Cihan understood it really helps as now I can focus on the Data Modelling
The relation ship is 1:M between Challenge and a ChallengeUser that is 1 Challenge can have many Challenge Users.