I'm fairly new to database design and mysql queries. I've been working with single table database structure up until this point. This current project is more a learning experience than a real world application but a stepping stone none the less... Here is my question:
I have two tables - items and counts. items contains the records for each item in our system, case pack, and item classification. counts contains a record for every time the item is cycle counted. I'm using php to interface with the database and write my query. counts.item_id references or matches items.id so that item specifics can be changed without much difficulty. I would like to run a query such as
"SELECT * FROM counts WHERE adj_num='5' ORDER BY $item_code"
The problem I run into is I want it to order the data in counts by the value of items.item_code in items using counts.item_id and items.id to tie them together.
Since I'm fairly new to this, would this be called a relation or key? Is this something that will work and do I need to 'relate' counts.item_id and items.id ?
TIA for wading through my post and helping a newbie.
Getting into what to call things gets complicated. Databases have several "levels" of discussion, ranging from very concrete (how do I get the desired information using MySQL x.yy), to the very abstract (how can I form a relation of tuples, such that it meets the constraints described by the user, presenting the result in a max2 dimensional structure). Depending on which level you are thinking, the terms can have more than one meaning, which makes it really tough to get a firm definition of what something means without first having a context for it!
To answer your concrete question, I'd start with:
FROM items AS i
LEFT JOIN counts AS c
ON (c.item_id = i.id)
ORDER BY i.item_code
I would describe this as two SQL tables, joined by a foreign key relationship. YMMV.