The formating was fine thanks.... The problem is that you want to get results from the vendor table twice. That is ok. But you have to reference and alias the table twice
So............
SELECT HIGH_PRIORITY
products.prod_name,
products.prod_vend1_id,
products.prod_vend2_id,
products.prod_category_id,
categories.prod_cat_name,
categories.prod_cat_desc,
vendors1.company_name,
vendors2.company_name,
FROM
products left join categories on products.prod_category_id = categories.id
left join vendors as venders1 on products.prod_vend1_id = vendors1.id
left join vendors as venders2 on products.prod_vend2_id = vendors2.id
WHERE
products.id = '$id'
....................
Check your results closely though and make sure everything you want is there and nothing you don't want.
Not sure if you are able to but a schema change might make future queries easier.
Something like..........
Products - table
id prod_name
1 ¦ Green Widget
2 ¦ Red Rag
3 ¦ Broom
4 ¦ Red Widget
5 ¦ Paint Brush
Vendors - table
id ¦ company_name
1 ¦ Cleaner Supplies
2 ¦ Widgets r Us
3 ¦ Widgets n Wotsits
4 ¦ Cleaneze Inc.
5 ¦ DIY Supplies
Categories - table
id ¦ prod_cat_name prod_cat_desc
1 ¦ Cleaning Cleaning products
2 ¦ Widgets All kinds of widgets
3 ¦ Decorating Decorating tools and materials
4 ¦ Building Builders Supplies
5 ¦ Electrical Electrical Supplies
TABLE product_has_vendor
product_id ¦ vendor_id
1 ¦ 2
1 ¦ 3
2 ¦ 2
2 ¦ 6
8 ¦ 3
8 ¦ 6
8 ¦ 9
8 ¦ 12
8 ¦ 15
8 ¦ 98
TABLE product_has_category
product_id ¦ category_id
1 ¦ 3
1 ¦ 7
2 ¦ 8
2 ¦ 2
8 ¦ 3
8 ¦ 98
This way you can have a true "one to many" relationship and you don't have to tack on a new field every time you want to add a vendor which will result in tables looking like vendor_id_1 vendor_id_2 vendor_id_3 vendor_id_4 vendor_id_5 vendor_id_6
That way your SQL would look like
SELECT
*
from
product_has_vendor, vendor, product
where
product_has_vendor.product_id = product.product_id
and product_has_vendor.vendor_id = vendor.vendor_id
and product.product_id = '$product_id'
There could be a million vendors for that product and they would all be returned here... the way you have it now it would be 1 'AND' statement per vendor_id_X in the product table.
Hope this helps... post back if not.
[edited by: Demaestro at 2:27 pm (utc) on Oct. 24, 2007]
