Results 1 to 9 of 9

Thread: How can i write this SQL

  1. #1
    Join Date
    Feb 2014
    Location
    Sympathy With Ukraine
    Posts
    8

    Post How can i write this SQL

    suppose i have the following tables

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

  2. #2

    Default Re: How can i write this SQL

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

  3. #3
    Join Date
    Jul 2008
    Location
    Seattle, WA
    Posts
    17,317

    Default Re: How can i write this SQL

    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.

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

  4. #4
    Join Date
    Feb 2014
    Location
    Sympathy With Ukraine
    Posts
    8

    Default Re: How can i write this SQL

    i'm using SQLite3
    my problem is a little more complicated but the example i provided is a more simple one,
    i've tried :

    Code:
    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

  5. #5

    Default Re: How can i write this SQL

    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.

  6. #6
    Join Date
    Mar 2014
    Location
    Maledives
    Posts
    4

    Lightbulb Re: How can i write this SQL

    If I understood your Tables and your questions right, then this SQL should be work:

    Code:
    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

  7. #7
    Join Date
    Sep 2013
    Location
    New York
    Posts
    198

    Default Re: How can i write this SQL

    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

  8. #8
    Join Date
    Feb 2014
    Location
    Sympathy With Ukraine
    Posts
    8

    Default Re: How can i write this SQL

    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.!!

  9. #9
    Join Date
    Sep 2013
    Location
    New York
    Posts
    198

    Default Re: How can i write this SQL

    Quote Originally Posted by bedo View Post
    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.
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •