I have two tables that stores product details which is carinfo and products.I did it in 2 separate tables as the products are by diffrent category and therefore the fields are different. I'm not sure if that was the correct way doing it coz i seem to have problem now.
The problem is,each product purchased are stored in a order table and that table only stores the product ID.
The items in the first two table which is carinfo and products, have both unique ID like p1 and c1.
My problem now is when i'm trying to get the product information of item in order table i need to check if that is from product table or carinfo.
Coz i wanted to generate a report that will list the item in order table together with the items information.
The sql i use is
" [B]select p.productid,p.prodname,p.proddescription,c.productid,c.prodname,c.proddescription from carinfo c,product p where [I]p.productid='p14'[/I] or [I]c.productID='p14'[/I] group by p.productid,p.prodname,p.proddescription,c.productid,c.prodname,c.proddescription"[/B]
You can view th example results here.
the results i get are the product information of the item if its in product table,but the quesry also results to list all the product name in carinfo table.
I hope my question is clear.
any help are really aprreciated.
Its urgent and i've been breaking my head thinking how to solve this without having to change my database design which is to combine both carinfo and product table as one.
thanks a lot.