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 [email protected].
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.
