Duplicating mysql information on SUM ...

pepelepew1962 · 05-22-2011, 08:29 PM

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.

tony · 05-22-2011, 09:02 PM

I think what you need to do explicitly make an inner join query something like

sql Code:

SELECT tblMain.tblMain_Main00, tblMain.tblMain_Main05, tblMain.tblMain_Main06, tblPart.tblPart_Part00, tblPart.tblPart_Part01, tblPart.tblPart_Part02
FROM tblPart
INNER JOIN tblMain ON tblMain.tblMain_Main00 = tblPart.tblPart_Part00
ORDER BY tblMain.tblMain_Main05, tblMain.tblMain_Main00

P.S. Just an advice but I find the prefix tbl and the double Part_Part naming a bit redundant and fills up with clutter, just a personal note.

wGEric · 05-26-2011, 05:34 PM

Sounds like a one-to-many relationship so you would need to do a left or right join. Inner join is used for a one-to-one relationship.

If you want to do SUM and other functions like that then you'll need a group by.

I agree with Tony. Name your columns something useful so that in the future it won't take a manual to figure out what data is in each column.