I got on the idea of writing a little post about this a while ago, but forgot about it. And just today I uncovered the note I had written to myself, to keep my promise to all of you, so here goes.
My situation was as follows: Imagine a store. Every product has a unique identification code (NASA-number), a product description and a price, to keep it very basic. Then, every product has a stock number (The amount of items currently in stock). We're keeping these values apart from eachother because keeping the amount of items in stock isn't enough. We also need to keep a log of everything that happens to the product (how many do we sell per day, how many are delivered to us, how much do we throw out).
So basically, we have two tables. One contains all product information, and the second keeps daily logs of what happens to each and every product. Let's give you a basic table layout:
CREATE TABLE `nasa` (
`nasa` INT(16) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`price` FLOAT NOT NULL,
`content` VARCHAR(255) NOT NULL,
UNIQUE KEY `nasa` (`nasa`)
);
This is the listing of the products, every single item has a unique NASA-number, names can overlap providing the content is different (think a litre of whole milk vs. a gallon of whole milk), than we have the log:
CREATE TABLE `history` (
`DAY` INT(16) NOT NULL,
`nasa` INT(16) NOT NULL,
`stock` INT(16) NOT NULL,
`received` INT(16) NOT NULL,
`sold` INT(16) NOT NULL,
`counted` INT(16) NOT NULL
);
So this one has a daily log. But if you want to display the data of these tables on your screen, you might be tempted to run two queries, one to fetch the name (for legibility, people aren't very likely to remember numbers), maybe the content and one to fetch the current stock (are we out? did we receive anything?). This would go as follows
SELECT name,content FROM nasa WHERE nasa=62334
and then
SELECT stock FROM history WHERE nasa=62334
What if you combined these two into a single query? First write down which things you need: name, content, sales and current stock. Then, lo and behold the power of the JOIN-statement.
SELECT name,content,stock,sold FROM nasa JOIN history ON nasa.nasa =
history.nasa WHERE history.nasa = 62334 AND history.DAY = today
See that? You can just select the four columns you need. MySQL will retrieve any row where the history.nasa matches the nasa.nasa so be sure to specify which row you want to retrieve. In this case you could also order descending by the date and limit the rows retrieved to 1:
SELECT name,content,stock,sold FROM nasa JOIN history ON nasa.nasa =
history.nasa WHERE history.nasa = 62334 ORDER BY history.DAY DESC LIMIT 1
Thanks for reading, hope it makes any sense :))
P.S. In case you were wondering, 62334 is an actual NASA number used in Albert Heijn. It's the code for fresh cod filet.
Popularity: 42% [?]
Web development
Recent comments