Help me with SQL query for export

[Jakub01]

Hi all,

I need help with SQL question from OSc DB for price compare machine. (similar froogle)

<?xml version="1.0" encoding="windows-1250"?>
<SHOP>
 <ID>96</ID>
<SHOPITEM>
 <MANUFACTURER>255 chars</MANUFACTURER>
 <PRODUCT>255 chars</PRODUCT>
 <DESCRIPTION>750 chars</DESCRIPTION>
 <URL>255 chars</URL>
 <PRICE>XXXXX.yy</PRICE>
 <VAT>0.22</VAT>
 <ISBN></ISBN>
 <DELIVERY>255 chars</DELIVERY>
 <DELIVERYMETHOD>255 chars</DELIVERYMETHOD>
 <EAN></EAN>
 <EAN1></EAN1>
 <CATEGORYTEXT>255</CATEGORYTEXT>
 <IMGURL>255 chars</IMGURL>
 <EXPIRES>yyyymmdd</EXPIRES>
</SHOPITEM>

 

1) I have written part of this, but I don know how join manufacturers, and category becouse they are in other tables.

 

2) When is product on special, how get price from specials instead products.products_price

 

 

SELECT d.products_id, d.products_name, d.products_description, p.products_price, p.products_image from products_description AS d, products AS p
WHERE  p.products_status=1 AND p.products_id=d.products_id AND p.category

 

Can anybobdy help me ?

Thanks, Jakub

 

 

It should be this:

<?xml version="1.0" encoding="windows-1250"?>
<SHOP>
<ID>96</ID>
<SHOPITEM>
<MANUFACTURER>$manufacturers.manufacturers_name</MANUFACTURER>
<PRODUCT>$product_description.product_description</PRODUCT>
<DESCRIPTION>$product_description.product_description</DESCRIPTION>
 <URL>http://oscommerce/product_info.php?products_id=$products.products_id</URL>
 <PRICE>$products.products.price</PRICE>
                               <VAT>0.22</VAT>
 <ISBN></ISBN>
 <DELIVERY>255 chars</DELIVERY>
 <DELIVERYMETHOD>255 chars</DELIVERYMETHOD>
 <EAN></EAN>
 <EAN1></EAN1>
 <CATEGORYTEXT>$categories_description.categories.name</CATEGORYTEXT>
 <IMGURL>http://oscommerce/images/$products.products_image</IMGURL>
 <EXPIRES>yyyymmdd</EXPIRES>
</SHOPITEM>