| | |
Hi, need advice on sql statement and table structure
Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
![]() |
•
•
Join Date: Jul 2006
Posts: 21
Reputation:
Solved Threads: 0
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
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.
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
"
select p.productid,p.prodname,p.proddescription,c.productid,c.prodname,c.proddescription from carinfo c,product p where p.productid='p14' or c.productID='p14' group by p.productid,p.prodname,p.proddescription,c.productid,c.prodname,c.proddescription"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.
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_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)
MySQL Syntax (Toggle Plain Text)
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)
Note to self... pocket cup
•
•
Join Date: Jul 2006
Posts: 21
Reputation:
Solved Threads: 0
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
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
![]() |
Similar Threads
- SQL statement Error (ASP)
- Syntax error in "like" in sql statement (ASP.NET)
- Question about SQL statement (MS SQL)
- sql statement (Visual Basic 4 / 5 / 6)
- run sql statement in asp (ASP)
Other Threads in the MySQL Forum
- Previous Thread: Mysql Version
- Next Thread: MySQL DB just DROPPED!
| Thread Tools | Search this Thread |
Tag cloud for MySQL
1 amazon api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright count court crm data database design developer development distinct drupal dui ec2 email enter enterprise eudora facebook form foss gartner gnu government gpl greenit groklaw groupware hiring hyperic images innerjoins insert ip joebrockmeier journalism keyword keywords kickfire laptop law legal license licensing linux managing mariadb matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla multiple music mysql mysqlcolumnupdating mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news opendatabasealliance opengovernment opensource operand oracle penelope php priceupdating query referencedesign reorderingcolumns resultset saas select simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency virtualization





