Query and sort multiple tables

fireman949 · #1 (**permalink**) 04-06-04, 15:12

Hello,
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

PHP Code:


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

Code:

TABLE - items
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(6)      |      | PRI | NULL    | auto_increment |
| item_code  | varchar(14) |      |     |         |                |
| case_pack  | int(5)      | YES  |     | NULL    |                |
| item_class | char(3)     | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


TABLE - counts
+-------------+-------------+------+-----+---------+----------------+
| Field          | Type          | Null  | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| count_id    | int(8)      |      | PRI | NULL    | auto_increment |
| item_id     | int(8)      | YES  |     | NULL    |                |
| date        | date        | YES  |     | NULL    |                |
| variance    | int(8)      | YES  |     | NULL    |                |
| adj_num     | int(5)      | YES  |     | NULL    |                |
| qty_counted | int(10)     | YES  |     | NULL    |                |
| book_qty     | int(10)     | YES  |     | NULL    |                |
| reason        | varchar(50) | YES  |     | NULL    |                |
| comments    | text        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
9 rows in set (0.23 sec)

Pat Phelan · #2 (**permalink**) 04-07-04, 11:43

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:

PHP Code:


			
SELECT *

   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.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech