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.  🙂