SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Duplicating mysql information on SUM ...

    Hello:

    I am having problems getting information from 2 tables and think this is a mysql issue instead of php. I have a MAIN table that lists computer models and another PART table that has it's components. My query result needs to be, list the model, manuf and type ( main ) and all it's components (parts ), including the unit price, the max unit price, the min unit price and the average unit price. My primary focus is with the PART table. The result will list the same model and manuf along with all the parts and prices. Maybe an example would help:
    //
    $ritelist2101 = "SELECT tblMain.tblMain_Main00, tblMain.tblMain_Main05, tblMain.tblMain_Main06, tblPart.tblPart_Part00, tblPart.tblPart_Part01, tblPart.tblPart_Part02 FROM tblMain, tblPart WHERE (tblMain.tblMain_Main00 = tblPart.tblPart_Part00) ORDER BY tblMain_Main05, tblMain_Main00";
    $ritelist2102 = mysql_query($ritelist2101);
    //
    Sample Data:
    Main00=125
    Main05=Sony
    Main06=Desktop
    //
    Main00=136
    Main05=Sony
    Main06=Desktop
    //
    Main00=142
    Main05=Dell
    Main06=Laptop
    //
    //
    Part00=125
    Part01=Drive
    Part02=500.00
    //
    Part00=125
    Part01=Video
    Part02=100.00
    //
    Part00=125
    Part01=Keyboard
    Part02=63.00
    //
    //
    Part00=136
    Part01=Drive
    Part02=450.00
    //
    Part00=136
    Part01=Video
    Part02=300.00
    //
    RESULT: (Manu-Model-Part-Price-Max-Min-Avg)
    Sony-125-DRIVE-500-500-63-221
    Sony-125-VIDEO-100-500-63-221
    Sony-125-KEYBOARD-63-500-63-221
    Sony-136-DRIVE-450-450-300-375
    Sony-136-VIDEO-300-450-300-375
    etc...
    //
    I fully understand that a lot of information is duplicated, but it is not my place to ask, but to do. Supposedly more calculations will be made, but first things first. Also, I need to read the data with:

    $Main00 = $row['tblMain_Main00']; // Main Model
    $Main05 = $row['tblMain_Main05']; // Manufacturer
    $Main06 = $row['tblMain_Main06']; // Type
    $Part00 = $row['tblPart_Part00']; // Part Model

    Can anyone please help me on this.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i read your post a couple of times and couldn't find the question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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