NoSQL small problems, RIGHT JOIN

[Tomek]

I have small problem with OrientDB, and I hope you'll help me.

There are classes:

Animals class:
----+-----+----+-------
#   |@RID |species|name  
----+-----+-------+------
0   |#13:0|dog    |Max  
1   |#13:1|cat    |Lulu  
2   |#13:2|snake  |zigzag
----+-----+-------+------


peson class:
----+-----+----+-------
#   |@RID |name|animals
----+-----+----+-------
0   |#14:0|John|[2]    
----+-----+----+-------
where [2] is [#13:0, #13:1]

how to get records with person and his animals names, something like RIGHT JOIN in SQL??
I would have an output similar to that:

----+-----+----+-------
#   |@RID |name|animals.name
----+-----+----+-------
0   |#????|John|Max 
1   |#????|John|Lulu 
----+-----+----+-------

Is there also something like CROSS JOIN?

And please tell me why this command doesn't work, and how to fix it.

update person add animals = (SELECT @rid FROM animals WHERE name='zigzag') where name = 'John'

[odbuser]

*updated join query so that it doesn't include animals that don't have a person.

If you are using the graph database you can do the following:

create class person extends V
create class animal extends V
create class animals extends E

create vertex animal set name='Max',species='dog'
create vertex animal set name='Lulu',species='cat'
create vertex animal set name='zigzag',species='snake'  
create vertex person set name="John"

create edge animals from ( select from person ) to ( select from animal where name in ["Max","Lulu"] )

select @rid as animal_rid, in('animals')[0].name as pn, name from animal where in('animals').size() <> 0

----+-----+----------+----+------
#   |@RID |animal_rid|pn  |name  
----+-----+----------+----+------
0   |#-2:1|#13:1     |John|Max  
1   |#-2:2|#13:2     |John|Lulu


The @RID is not a real rid but you can get it from the @rid field.
in('animals') is a function that follows the in_animals property on the record and results in a collection.  You can get the first item in the collection using [0] (note the "first" function should also work but unfortunately using it limits the results to one record instead of just the first item of the collection field... I think that's a bug).

For the "cross join" if you are using it like an inner join then it's the same as above.  If it's a real cross join, I'm not sure how to do that.

To add an edge using the graph database:

create edge animals from ( select from person where name = 'John' ) to ( select from animal where name='zigzag' )

[Tomek]

Thank you for your reply,

I'm using document database, but also I'm going to use graphs soon, so your response is interesting for me too.

But how to make something like RIGHT JOIN in document-oriented database?

[MrFT]

Your question is related to another question

I have the same feeling, that OrientSQL is missing some kind of JOIN syntax.

It would be useful 

• for analysing your data
• and for creating new (previously inexistent) edges for whole sets of classes in one go (instead of having to write a loop in a programming language).

Hope it gets added soon...

Op dinsdag 26 augustus 2014 16:00:06 UTC+2 schreef Tomek:

Thank you for your reply,

I'm using document database, but also I'm going to use graphs soon, so your response is interesting for me too.

But how to make something like RIGHT JOIN in document-oriented database?