Page 1 of 1

Tables Joins - Processing and Displaying the Resulting Array

Posted: Thu Sep 17, 2009 11:10 am
by chrispynic99
Howdy,

I am having trouble wrapping my brain around joins and processing the resulting array. Here are the tables with some sample data:

table 1
-------------------------------------------
| title | description |
-------------------------------------------
| Best | This is a really good one |
| Good | This one is good too |
-------------------------------------------

table 2
--------------------------
| ID | Name |
--------------------------
| 21 | Best |
| 22 | Best |
| 23 | Best |
| 24 | Best |
| 41 | Good |
| 42 | Good |
| 43 | Good |
--------------------------

table 3
----------------------
| e_id | value |
----------------------
| 21 | 1100 |
| 22 | 1200 |
| 23 | 1100 |
| 24 | 1000 |
| 41 | 550 |
| 42 | 570 |
| 43 | 560 |
----------------------

Here is the desired output:

Best - This is a really good one - 1200
Good - This one is good too - 570

Notice that 1200 and 570 are the max values from table 3 WHERE table1.title = table2.Name AND table2.ID = table3.e_id

I just can't figure out how to display this correctly. Here is what I have so far if this helps, or you can suggest another method:

Code: Select all

 
 $result = mysql_query(
            " SELECT * from table1, table2, table3 WHERE table1.title = table2.Name AND table2.ID = table3.e_id; ");
  if (!$result) {
    echo("<P>Error performing query: " .
         mysql_error() . "</P>");
    exit();
  }
 
 // Display
  while ( $row = mysql_fetch_array($result) ) {
    echo( $row["title"]." - ".$row["description"]. " - " );
  }
 
?>
 
 
Thanks in advance for your help.

Re: Tables Joins - Processing and Displaying the Resulting Array

Posted: Thu Sep 17, 2009 11:29 am
by jackpf
Do you not just want to select `table1`.*?

What does what you've got display?

Re: Tables Joins - Processing and Displaying the Resulting Array

Posted: Thu Sep 17, 2009 12:01 pm
by chrispynic99
I need to pull the max value from table 3, so I can't just select table 1.

Re: Tables Joins - Processing and Displaying the Resulting Array

Posted: Thu Sep 17, 2009 12:49 pm
by califdon
Use the SQL function MAX() http://www.tizag.com/mysqlTutorial/mysqlmax.php

But even before that, your table structure doesn't make any sense to me. What entity is represented by tables 2 and 3? If you don't have clear definitions of what every table represents, you will get absolutely nowhere in relational databases. The reason you are having trouble figuring out the joins is almost surely that your tables don't represent distinct entities, as required by the relational model on which SQL and all relational database operations are based.