items_data
==========
int primary id,
string name,
double price
orders_data
==========
int primary id,
Date date,
orders_details_increase
================
int primary id,
int order_id,
int item_id,
int quantity,
double sale_price,
double value
orders_details_decrease
================
int primary id,
int order_id,
int item_id,
int quantity,
double sale_price,
double value
i wana get how many orders that have an item with id = 1???
> i wana get how many orders that have an item with id = 1???
Part of it is going to depend on how the tables are properly joined in a
SQL query, assuming you can’t just pull the information from a single
table.
This /appears/ to be a homework question - so rather than provide a
specific answer, perhaps forum members here can help guide you to the
solution.
What sorts of things have you tried in building your query? As ab said,
knowing what RDBMS you’re using will help, but if it’s a general SQL
query question (as it might be in a homework assignment for a database
class), that’s OK too, but understand that we’re not here to do homework
assignments.
i’m using SQLite3
my problem is a little more complicated but the example i provided is a more simple one,
i’ve tried :
SELECT COUNT(orders_data.id) FROM orders_data, orders_details_increase, orders_details_decrease WHERE (orders_details_increase.account_id = 1 OR orders_details_decrease.account_id = 1)
AND (orders_data.id = orders_details_increase.entry_id OR orders_data.id = orders_details_decrease.entry_id);
I’m not a a SQLite3 guy, but if I were you I would look into using joins. Usually these questions are best on a database forum, plus if you would like assistance anywhere I would include the create table statements, insert statements, and your query to assist others in reproducing your issue.
If I understood your Tables and your questions right, then this SQL should be work:
select count(odd.id) from order_data od
left outer join orders_details_increase odi on od = odi.order_id
left outer join orders_details_decrease odd on od = odd.order_id
left outer join items_data id1 on id1.id = odi.item_id and id1.id = 1
left outer join items_data id2 on id2.id = od2.item_id and id2.id = 1
you have to join this Tables and you have top jon the items_data table twice!
I am not 100% sure what this question is about
but it looks like the database and maybe the application using it not very good designed for the task at hand.
#1. why are you using 2 order details tables ?
are you separating order and return ?
items ordered go into “orders_details_increase” and returned go into “orders_details_decrease” ?
OR what ?
any how it does not really matter if your query is a single item use and you will pass the itemid directly
simple query would be
SELECT distinct COUNT(order_id) as ordercount FROM
(select order_id from orders_details_increase where item_id = 1
union select distinct order_id from orders_details_increase where item_id = 1 )
but I suspect this is not as simple as it appears to be
well, i’ve managed to get around the problem (using long/nested ugly/painful loops)
but any way all the code snippets provided by vl1969 and Just4Work is pretty much useful even to learn from,
thank you all for considering reply to my thread.!!
you are welcome bedo, but if you ask the right question you will get a better answer.
why do you need [quote]long/nested ugly/painful loops\quote] ?
do you actually need to get a count for ALL products ordered per product?
SQLLite should have almost all capability of full SQL. as far as I understand it has limited capability for advanced options like updateable views and such and it does not support full and right joins.
if you need an order count for all products by product than
this should do it with out loops , but keep in mind that as the tables grow this will become slower in time. maybe a database redesign is in order.
SELECT item_id,i.name, COUNT(order_id) as ordercount FROM
(select order_id, item_id from orders_details_increase
union select order_id, item_id from orders_details_decrease ) as md
left join items_data i on md.item_id = i.id
group by item_id,i.name
