SQL 3 and n:m relationships
От | Guillaume Rousse |
---|---|
Тема | SQL 3 and n:m relationships |
Дата | |
Msg-id | 00011100010200.01376@agathe обсуждение исходный текст |
Список | pgsql-general |
SQL3 bring array types, ideal for creating n:m relations directly, without crossing table. The question is : how to retrieve all linked records with one only request ? Let's have an exemple with books and authors with SQL 2 : CREATE TABLE books(id INT, name VARCHAR) CREATE TABLE authors(id INT, name VARCHAR) CREATE TABLE authorship(bookRef INT, authorRef INT, order INT) The following query returns all authors from book n�x: SELECT name FROM auhtors WHERE authorship.authorRef=author.id AND authorship.bookRef=x ORDER by authorship.order Now, with SQL3, only two tables are enough : CREATE TABLE books(id INT, name VARCHAR, authorsRef INT[]) CREATE TABLE writers(id INT, name VARCHAR) How then retrieve all authors of book n�x in SQL ? With a programming language, one can use a loop, and then send one query for every value found in books.authorsRef[]. Not very clean Or forge a query string with an OR statement, as SELECT name FROM authors WHERE author.id=book.authorRef[1] OR author.id=book.authorRef[2] and so on, but then lose the correct order. Not satisfying neither. So, what's the solution ? -- Guillaume Rousse ******************************************* Iremia - Universit� de la R�union 15 avenue Ren� Cassin, BP 7151 97715 Saint Denis, messagerie cedex 9 Tel:0262938287 Fax:0262938260 ICQ:10756815 Mail:Guillaume.Rousse@univ-reunion.fr BRIDGEKEEPER: What... is the air-speed velocity of an unladen swallow? ARTHUR: What do you mean? An African or European swallow?
В списке pgsql-general по дате отправления: