Join on non-key element

SQL++
1

Hi!
I’m looking for a way to join on non-key elements.

To give a complete example, I would like to have the list of airport names where I can go starting from SEA.

Is this possible using N1QL ? I can’t find a way!

2

After a lot of tests, i cam across this:

SELECT distinct(r.airportname) FROM 'travel-sample' r WHERE r.type = "airport" AND r.faa WITHIN ( SELECT distinct(rr.destinationairport) FROM 'travel-sample' rr WHERE rr.type = "route" AND rr.sourceairport='SEA' ) ORDER BY r.faa

Isn’t it a better way of doing it ?

3

Not sure which version you are suing but if you are on 4.5 preview, I assume you have looked at this page for examples:
http://developer.couchbase.com/documentation/server/4.5-dp/flexible-join-n1ql.html

The page has JOIN syntax we now support in 4.5 that allows left or the right side to specify the driving JOIN attribute.
thanks
-cihan

4

Hi @cambierr,

Unfortunately, all N1QL JOINs, including the new JOINs in 4.5, require the primary key of at least one term. In the travel-sample data, it would be possible to use the FAA code as a primary key for airports, assuming these codes are immutable. But the travel-sample doesn’t come that way out of the box, so something like your query is the way to do it in N1QL. I modified your query below to make it slightly more efficient:

SELECT DISTINCT r.airportname
FROM 'travel-sample' r 
WHERE r.type = "airport" AND r.faa IN (
SELECT DISTINCT RAW rr.destinationairport FROM 'travel-sample' rr WHERE rr.type = "route" AND rr.sourceairport='SEA'
)
ORDER BY r.faa

The following two indexes would help:

CREATE INDEX idx_name ON `travel-sample`( faa, airportname, type );

CREATE INDEX idx_source ON `travel-sample`( sourceairport, destinationairport, type );

Finally, your example surfaced a separate gap that we will fix for the upcoming beta, thanks! (Handling of IN for indexes).

5

@cihangirb,

Thank you for that link, it will be usefull. I’m just not sure about what solution will have the best performances ?

@geraldss,

What about the link of @cihangirb ? Otherwise, thank you for perf improvements (I didn’t know about RAW)

Btw, what’s the difference between SELECT DINSTINCT … and SELECT DISTINCT(…) ?

6

Hi @cambierr, no difference, the parentheses are optional. The links are for documentation, which you should read. You now have the solution, so you are all set.