I was able to work on a couple of MySQL queries this past week, which proved fun. The scenario is that we have an opportunity to sell certain items (for example a sweatshirt, a t-shirt, and a hat). People can pay either by credit card online or they can mail a check in for payment (meaning that they would still owe money when you run a report IF we have not received their check yet). The sweatshirts and t-shirts come in different sizes (small, medium, and large).
So, there is a need to generate a report that would show who ordered a sweatshirt, a t-shirt, or a hat, what size they ordered, and whether they have paid already (by credit card online) or not (mailing a check in but not yet received).
A co-worker had developed a query to find all of the people who had purchase a sweatshirt, t-shirt, or hat. She also developed another query to find who had paid already. The trick is to combine these 2 queries to show everyone that ordered AND if they have paid AND to show if they ordered 2 t-shirts, but 1 was a Large and 1 was a Small. This last part is required to help with inventory management, so that our coordinator (overseeing this project) would know how many of what size to order.
I was able to refine the query a little by using a nested INNER JOIN within a LEFT JOIN, but it still left out the people who had ordered something, but not paid yet. This is a query that is joining 2 tables and 1 view. I am going to revisit it this week, as time was short last week to work on this further.
I think my lack of familiarity with MySQL is hindering my progress a bit – it takes me longer to figure something out than it would if someone worked with MySQL on a daily basis. But, the good thing is, I am learning and that’s progress. 🙂