I have a bucket with multiple records differentiated by a single column called type.
I need to join multiple such documents together . Can I run a join on the same collection?
Select *
FROM sample AS t1
JOIN sample AS t2
ON t1.id = t2.airportId
WHERE t1.type='airport' AND t2.type='airplane';
Yes. e.g.
SELECT *
FROM `travel-sample` AS t1
JOIN `travel-sample` AS t2
ON t1.faa = t2.sourceairport
WHERE t1.type = 'airport' AND t2.type = 'route';
works with the supplied sample.
If you’re on 7.x and using collections, you could alternatively of course use, e.g.
SELECT *
FROM `travel-sample`.inventory.airport AS t1
JOIN `travel-sample`.inventory.route AS t2
ON t1.faa = t2.sourceairport;
Indexes will be required to support the join, the advisor can suggest the necessary:
ADVISE SELECT * FROM `travel-sample` AS t1 JOIN `travel-sample` AS t2 ON t1.faa = t2.sourceairport WHERE t1.type = 'airport' AND t2.type = 'route';
HTH.
Hey thank you, I had to create an index on type .