So, we have an interesting challenge with VIEWS 2 and the needs of the SLSA catalogue.

The problem stems from a very simple requirement: get the ceiling price of a product and display it in a table listing all products for a given taxonomy.

In an ideal world, the following SQL query would be used. A fairly straight forward process in any circumstance other than ours.


SELECT product.model AS model_number, supplier.reference AS supplier_name,
MAX(product_supplier_join.price) AS ceiling
FROM product_table product
JOIN product_supplier_join_table product_supplier_join ON product.model = product_supplier_join.product_model
JOIN supplier_table supplier ON product_supplier_join.supplier_ref
GROUP BY model_number, supplier_name


You'll note the use of MAX() and the GROUP BY features.

Since we have some radically different table and field names, we'll have to map this statement to real world tables and fields. The following demonstrates what the SQL should look like when using our VIEW:


SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language,
uc_products.model AS uc_products_model,