Select from 2 fields in different tables

[futurestar]

I'm hoping this is an easy one for your guys, as I do not deny being a big time noob.

 

MySQL Version: 5.1.52

 

Here is the current select statement that is working properly:

$row['Note'] = $db->getOne("SELECT comments FROM ".TABLE_ORDERS_STATUS_HISTORY."
WHERE orders_id = '$row[OrderNumber]' ORDER BY orders_status_history_id LIMIT 1");

 

I want to add another field source to combine with the "comments" field. The other field(s) is/are in a separate table and is called "product_options_values".

 

All of the select fields are text type fields. The field it will be inserted into is a text type field as well.  Here is what I attempted to no avail...

 

$row['Note'] = $db->getAll("SELECT comments FROM ".TABLE_ORDERS_STATUS_HISTORY." AND product_options_values FROM ".TABLE_ORDERS_PRODUCTS_ATTRIBUTES."
WHERE orders_id = '$row[OrderNumber]'");

 

So basically if the field named "comments" contained the data "testing only" and there were 2 corresponding product_options_values that were "small" and "brown", I am attempting to get the following:

"testing only small brown"

 

Any separators like a bar between the fields is a bonus, i.e.

"testing only | small | brown"

 

Your help is greatly appreciated!!!

[blacknight]

$query = "SELECT `osh`.`comments`, ".
"GROUP_CONCAT( DISTINCT CONCAT(`opa`.`product_options_values`)) as com ".
"FROM ".TABLE_ORDERS_STATUS_HISTORY." AS osh ".
"LEFT JOIN ".TABLE_ORDERS_PRODUCTS_ATTRIBUTES." AS opa ON".
" `oap`.`orders_id` = '$row[OrderNumber]' AND `osh`.`orders_id` = '$row[OrderNumber]'".
" ORDER BY orders_status_history_id LIMIT 1";

$row['Note'] = $db->getAll($query);

echo $row['Note']['comments'] .'|'.$row['Note'] ['com'][0].'|'.$row['Note']['com'][1].'<br>'; 

 

that should.. work....

[futurestar]

blacknight, thank you very much for your assistance, it is much appreciated.  Unfortunately I am getting an error when using your code.  The error in the log file is:

Apr 6, 2011 9:14:17 AM 

STDOUT: GlobalHardErrorException.<init> Failed to parse server's response: Expected methodResponse element, got br

 

If you have time to debug, I have attached the entire script to this posting, as I may have inadvertently not provided you enough information on my first post. 

 

The original code is commented out and begins at line 357 and ends at line 360.

Your code begins at line 362 and ends at line 373

 

Thank you!

 

[attachment deleted by admin]

[blacknight]

372 has a space "$row['Note'] ['com'][0]" should be "$row['Note']['com'][0]" my bad .... that could be the issue is nto try replacing line 364 with

"GROUP_CONCAT( `opa`.`product_options_values` SEPARATOR ‘|’) as com ".