Nested Queries
What is a Nested query?
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.
Views
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.
Nested Example 1 Part 1 - Query Design
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:
Example 1 Part 1 - Implementation
Nested Example 1 Part 2 - Query Design
Fields and calculations: *
Tables: Item,HardDrives
Search Criteria: item.stockLevel < harddrives.AverageLevel and category = 'Hard Drive'
Grouping:
Sort Order:
Example 1 Part 2 - Implementation
Nested Query - Example 2
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.
Example 2 Part 1 - Query Design
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
Example 2 Part 1- Implementation
Example 2 Part 2 - Query Design
Fields and calculations: AVG(cust3orders.TotalValue)
Tables: cust3orders
Search Criteria:
Grouping:
Sort Order: