Join records from the same table

[webrob]

I am using ZenCart and have created a custom query for retrieving order data. The only problem is that when I have products with attributes it creates 2 records with the same order. This is because that each product gets a record for the attributes it gets.

Here is my query:

SELECT 
zo.orders_id as v_orders_id,
customers_id as v_customers_id,
customers_name as v_customers_name,
customers_company as v_customers_company,
customers_street_address as v_customers_street_address,
customers_suburb as v_customers_suburb,
customers_city as v_customers_city,
customers_postcode as v_customers_postcode,
customers_country as v_customers_country,
customers_telephone as v_customers_telephone,
customers_email_address as v_customers_email_address,
date_purchased as v_date_purchased,
orders_status_name as v_orders_status_name,
products_model as v_products_model,
products_id as v_products_id,
products_name as v_products_name,
products_options as v_products_options,
products_options_values as v_products_options_values
FROM ".TABLE_ORDERS_PRODUCTS." zop LEFT JOIN ".TABLE_ORDERS_PRODUCTS_ATTRIBUTES." opa
ON(zop.orders_products_id = opa.orders_products_id), ".TABLE_ORDERS." zo, ".TABLE_ORDERS_STATUS." zos
WHERE zo.orders_id = zop.orders_id
AND zop.orders_id BETWEEN '1' AND '800'
AND zo.orders_status = zos.orders_status_id
AND zop.products_id = '147' 

I need to have all of the results for the specific product end up on the same line in my export. How can I accomplish this?

Thanks in advance!

[webrob]

I am using a class that helps me output the following

<table>
&#8722;
    <ROW>
<V_ORDERS_ID>796</V_ORDERS_ID>
<V_CUSTOMERS_ID>509</V_CUSTOMERS_ID>
<V_CUSTOMERS_NAME>rob milburn</V_CUSTOMERS_NAME>
<V_CUSTOMERS_COMPANY/>
<V_CUSTOMERS_STREET_ADDRESS>2511 florida</V_CUSTOMERS_STREET_ADDRESS>
<V_CUSTOMERS_SUBURB>5</V_CUSTOMERS_SUBURB>
<V_CUSTOMERS_CITY>huntington beach</V_CUSTOMERS_CITY>
<V_CUSTOMERS_POSTCODE>92648</V_CUSTOMERS_POSTCODE>
<V_CUSTOMERS_COUNTRY>United States</V_CUSTOMERS_COUNTRY>
<V_CUSTOMERS_TELEPHONE>714-849-3124</V_CUSTOMERS_TELEPHONE>
<V_CUSTOMERS_EMAIL_ADDRESS>rob@piermarketing.com</V_CUSTOMERS_EMAIL_ADDRESS>
<V_DATE_PURCHASED>2008-02-29 13:29:55</V_DATE_PURCHASED>
<V_ORDERS_STATUS_NAME>Processing</V_ORDERS_STATUS_NAME>
<V_PRODUCTS_MODEL/>
<V_PRODUCTS_ID>147</V_PRODUCTS_ID>
<V_PRODUCTS_NAME>Business Cards</V_PRODUCTS_NAME>
<V_PRODUCTS_OPTIONS>Name</V_PRODUCTS_OPTIONS>
<V_PRODUCTS_OPTIONS_VALUES>rob milburn</V_PRODUCTS_OPTIONS_VALUES>
</ROW>
&#8722;
    <ROW>
<V_ORDERS_ID>796</V_ORDERS_ID>
<V_CUSTOMERS_ID>509</V_CUSTOMERS_ID>
<V_CUSTOMERS_NAME>rob milburn</V_CUSTOMERS_NAME>
<V_CUSTOMERS_COMPANY/>
<V_CUSTOMERS_STREET_ADDRESS>2511 florida</V_CUSTOMERS_STREET_ADDRESS>
<V_CUSTOMERS_SUBURB>5</V_CUSTOMERS_SUBURB>
<V_CUSTOMERS_CITY>huntington beach</V_CUSTOMERS_CITY>
<V_CUSTOMERS_POSTCODE>92648</V_CUSTOMERS_POSTCODE>
<V_CUSTOMERS_COUNTRY>United States</V_CUSTOMERS_COUNTRY>
<V_CUSTOMERS_TELEPHONE>714-849-3124</V_CUSTOMERS_TELEPHONE>
<V_CUSTOMERS_EMAIL_ADDRESS>rob@piermarketing.com</V_CUSTOMERS_EMAIL_ADDRESS>
<V_DATE_PURCHASED>2008-02-29 13:29:55</V_DATE_PURCHASED>
<V_ORDERS_STATUS_NAME>Processing</V_ORDERS_STATUS_NAME>
<V_PRODUCTS_MODEL/>
<V_PRODUCTS_ID>147</V_PRODUCTS_ID>
<V_PRODUCTS_NAME>Business Cards</V_PRODUCTS_NAME>
<V_PRODUCTS_OPTIONS>Address</V_PRODUCTS_OPTIONS>
<V_PRODUCTS_OPTIONS_VALUES>2511 florida</V_PRODUCTS_OPTIONS_VALUES>
</ROW>
</table> 

Now I just need to figure out how to combine this line <V_PRODUCTS_OPTIONS_VALUES> becuase as it is now, this is jsut the 1 order, but since there are multiple <V_PRODUCTS_OPTIONS_VALUES> it creates new records for each instance.

Any ideas?