Sample 7: Advanced SQL queries

As we have stated before, data is pulled using a recordset, so any kind of relationship is available.

In this example we'll use one single table, where all the data is related whithin the same table using a "parent" field.

 

Our table "products" will look like this:

You should note the relationship beween "parent" and "id" fields.

Our SQL querywill create 4 tables, "types", "genres", "authors" and "items" by cloning the original one. After this, whe'll establish the relationships in the same query:

 

SELECT types.label AS type, types.id AS type_id, genres.label AS genre, genres.id AS genre_id, authors.label AS author, authors.id AS author_id, items.label AS item, items.id AS item_id FROM products AS types, products AS genres, products AS authors, products AS items WHERE items.parent=authors.id AND authors.parent=genres.id and genres.parent=types.id

 

After this, we can create the menus:

And here is the result: