Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Walls, MS
    Posts
    3

    Unanswered: Query and sort multiple tables

    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)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •