Sometimes you will need to use the results of a previous query to find a further example.
For example finding the minimum value then finding all records with that minimum value. This would be similar to finding a minimum in Python then trying to find all records with that minimum value - you couldn't do it in a single operation. You would have to go through the array one time to find the minimum and then go through the array again to display everything with that minimum value.
In MySQL we will use a new statement called a VIEW which will essentially create a table with the value(s) that we need. We can then use this as another table in a further query.
The sales team want to display the description and supplier of all Hard Drives with number in stock less than the average.
Fields and calculations: avg(stocklevel)
Tables: Item
Search Criteria: category = 'Hard Drive'
Grouping:
Sort Order:
Fields and calculations: *
Tables: Item,HardDrives
Search Criteria: item.stockLevel < harddrives.AverageLevel and category = 'Hard Drive'
Grouping:
Sort Order:
Average value of orders made by customer 3. to do this first we will have to calculate the total value of each order. Then we can find the average total value.
Fields and calculations:
forename, surname,OrderItem.orderID, SUM(item.price * OrderItem.quantity) AS 'TotalValue'
Tables: CustomerOrder,item,Customer,OrderItem
Search Criteria: CustomerOrder.customerID = '3'
Grouping: orderID
Sort Order:
Explanation
We need to calculate the SUM of the orders as we cannot use an aggregate function in the WHERE clause
Fields and calculations: AVG(cust3orders.TotalValue)
Tables: cust3orders
Search Criteria:
Grouping:
Sort Order: