How can i write this SQL

suppose i have the following tables

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???

Welcome to the forums.

What RDBMS are you using? What have you tried so far? What has been the
output if you have received errors in your attempts?


Good luck.

If you find this post helpful and are logged into the web interface,
show your appreciation and click on the star below…

On Wed, 12 Mar 2014 00:16:01 +0000, bedo wrote:

> 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. :slight_smile:

Jim

Jim Henderson
openSUSE Forums Administrator
Forum Use Terms & Conditions at http://tinyurl.com/openSUSE-T-C

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);

but i got more records count than expected

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!

Hope 'its clear and helpful

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