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?