The blog of Steve Root

  • creating a new column in a MySQL select query

    I’ve been really busy since my last post (a whole month without a post!). Work is exceptionally busy and work comes first. Even my reading has slowed down a bit. I have several things waiting to be posted when I get time to write about them – including the first ever competition for this blog! You’ll have to check back over the next week or two to find out what that’s all about, in the mean time this post is all about…

    i-049902d1550e456dd21b47b1967a27cb-mysql_100x52-64.gif

    “creating a new column in a MySQL select query”

    So, here I am manipulating some data from a supplier to make it work with our internal business systems. Essentially I had to work out how much a kitchen cabinet would cost given an “assembly list”, “component list”, “decision list” and of course “cup of coffee”.

    Assembly list tells me that each unit needs a cabinet and a door. There are lots of door colours and lots of cabinet colours. These options are all in the component list which tells me the price of each door. The decision list is my own creation and says “build me a unit using a Maple door and a Maple cabinet”. “cup of coffee” feeds my habit while I code. So far, so good.

    Once I’ve created a list of units using maple doors and maple cabinets, I then want to create another list of oak doors and oak cabinets. Join them all together and I have a complete list of cabinets that customers can buy. I needed a way of identifying which cabinet was built with which options and for the life of me I couldn’t remember how to do it. I needed to create an additional column in my query that would record what “decision list” row had been used for this build.

    The answer is of course obvious once you know it:
    Select “StevesDecision” as StevesColumnName, FirstBuildUpStaticTable.Material, SUM(`Gross weight`) As GWeight, and so on.

    “StevesDecision” becomes the content of each row and “StevesColumnName” becomes the column name. All the rest of the select… line remains the same.

  • Sharpe's Havoc (Bernard Cornwell), Sharpe's Tiger (Bernard Cornwell) & Sharpe's Eagle (Bernard Cornwell)



    I have been busy reading. Just before we left for our holiday I scoured the local second hand bookshop and picked up 3 more Sharpe books. I’ve been aiming for the beginning of the series and I did quite well.

    Firstly I started with Sharpe’s Tiger which (being chronologically first in the series) tells the story of private Sharpe and his first adventure – rescuing a senior officer held in a citadel. Sharpe is uncannily lucky, for not only does he rescue the officer and therefore get his promotion to sergeant, he manages to kill the Tippoo (Tippoo is like the King) and get some of his jewels.

    Then I moved onto Sharpe’s Eagle (being the first written in the series) and it tells the story of how Lieutenant Sharpe becomes Captain Sharpe at the battle of Talavera. Once again he survives against all odds as well as against enemies from within his own army. He also loses yet another women he has fallen in love with. It seems poor Sharpe is as unlucky in love as he is lucky in battle.

    Finally, Sharpe’s Havoc, which is set just before Sharpe’s Eagle as he fights through northern Portugal.

    I have loved all of these books, as much for the historical note at the end of the book as for the fiction itself. Bernard Cornwell highlights all the changes he has made to the real events and describes a little of the places as they are today.

    I also think it amazing that a series can be written in such apparent disorder yet knit together so neatly. Sharpe’s Eagle was first published in 1981, yet it almost perfectly cross references with events in Sharpe’s Havoc of 2003.

  • Warrier Class (Dale Brown)

    Another Dale Brown novel, but this time not part of the “Dreamland” series of novels. At least it doesn’t say ‘Dreamland’ on the front cover but it does have all the hi tech gadets and gizmos and the ‘dreamland’ base is used too. In these books, people can jump out of airplanes without parachutes and survive. They (the American armed forces, mostly airforce) spend their time chasing after a new stealth aircraft built by a rich Russion oil/drugs magnate. Guess who wins? You were going to say the US Air Force right? You better read it then, you see there’s this group of rebels from…. oh, better not say!

    I think I’ve prefered some of his other books over this one, but it still made for a good read.

Search this site

Free apps

  • birthday.sroot.eu – Your birthday or other celebration date based on [years on other planets] / [how many seconds/days] / [how far you’ve travelled around the sun]
  • stampulator.sroot.eu – Calculates the combination and how many 1st, 2nd, large 1st and large 2nd class Royal Mail stamps you need on large envelopes and packets

Recent posts

Archives

Categories