Hi, need advice on sql statement and table structure

[newMeg]

Hi,

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.

http://www.ineers.com/meg/error.JPG

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.

[pty]

In my opinion all things that can be ordered should be treated the same, so you'd have a table of orders, a table of items (this can include cars), and a table of categories (cars, parts, gifts etc)

orders --< order_lines --- items >--- categories

orders: order_id, customer_id, date
order_lines: order_line_id, order_id, item_id
items: item_id, name, description, weight, price
categories: category_id, name, description

From a data point of view this should be cleaner; if you need to store different information about items you can always add another table in with info specific to that category (ie engine size, number of seats etc)

[newMeg]

Hi, thanks a lot for the reply. But i dont actually get wat u mean. I really need to solve this if by at least changing the sql and not changing the table structure as i will need to change my codes that involve the table.
I really hope you see the problem in my sql so that i get the product info of only that the productid fulfills.

I was not aware of this problem till yesterday and im really out of time.

thanks again