•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 391,656 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,763 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser:
Views: 1618 | Replies: 2
 |
•
•
Join Date: Jul 2006
Posts: 15
Reputation: 
Rep Power: 3
Solved Threads: 0
Newbie Poster
Hi, need advice on sql statement and table structure 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.
•
•
Join Date: Oct 2005
Location: Manchester, UK
Posts: 481
Reputation:
Rep Power: 3
Solved Threads: 31
Posting Pro in Training
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)
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: 15
Reputation:
Rep Power: 3
Solved Threads: 0
Newbie Poster
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
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
Linear Mode
- 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!
