[orientdb] How to make a query using related data from three class

[TomekK]

How can I get the same effect in OrientDB query as with JOIN SQL in 
relational databases?

I have classes in OrientDB
CREATE Class Continent;
CREATE Class Country;
CREATE Class City;

//Hong Kong
INSERT INTO  City set name = 'Tsuen Wan';
INSERT INTO  City set name = 'Sha Tin';
INSERT INTO  City set name = 'Tuen Mun New Town';

//Denmark
INSERT INTO  City set name = 'Copenhagen';0
INSERT INTO  City set name = 'Aarhus';
INSERT INTO  City set name = 'Odense';

//France
INSERT INTO  City set name = 'Paris';
INSERT INTO  City set name = 'Marseille';
INSERT INTO  City set name = 'Lyon';

INSERT INTO  Country set name = 'Hong Kong', cities=(SELECT FROM City WHERE 
name in ['Tsuen Wan', 'Sha Tin', 'Tuen Mun New Town']);
INSERT INTO  Country set name = 'Denmark', cities=(SELECT FROM City WHERE 
name in ['Copenhagen', 'Aarhus', 'Odense']);
INSERT INTO  Country set name = 'France', cities=(SELECT FROM City WHERE 
name in ['Paris', 'Marseille', 'Lyon']);

INSERT INTO  Continent set name='Asia', countries=(SELECT FROM Country 
WHERE name = 'Hong Kong');
INSERT INTO  Continent set name='Europe', countries=(SELECT FROM Country 
WHERE name in ['Denmark', 'France']);

which is the equivalent of the SQL tables
CREATE TABLE Continent (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);


CREATE TABLE Country (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    continent_id INT,
    foreign key (continent_id) references Continent(id)
);

CREATE TABLE City (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    country_id INT,
    foreign key (country_id) references Country(id)
);

How to make a query using data from these three classes, which will return
CITY  COUNTRY  CONTINENT  
Copenhagen Denmark Europe
Aarhus Denmark Europe
Odense Denmark Europe
Paris France Europe
Marseille France Europe
Lyon France Europe
Tsuen Wan Hong Kong Asia
Sha Tin Hong Kong Asia
Tuen Mun New Town Hong Kong Asia

Something like in SQL
SELECT city.name AS City, country.name AS Country, continent.name AS 
Continent
FROM City city, Country country, Continent continent
WHERE
city.country_id=country.id and continent.id=country.continent_id;


-- 

--- 
You received this message because you are subscribed to the Google Groups 
"OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to orient-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/orient-database/c2244adb-5e2f-449d-8d40-7e6b22208d35%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.