1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

add item whose reference is in another table

Discussion in 'SQL PL/SQL' started by mecctro, Dec 13, 2011.

  1. mecctro

    mecctro Guest

    My English is not very good so sorry for mistakes. say I have table 'article':

    arrtno descr unit sales

    1 beer 1 10

    2 coke 1 12

    3 beer ct 12 5

    4 coke ct 12 7

    and table 'mutations'

    artno mutation

    1 3

    2 4

    I need to query sales for beer so it would be 10*1 + 5 * 12. So How to get mutation pair from other table and add it to basic item.
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi,
    As per your tables mentioned and requirement I have developed a query which gives you appropriate result. Please go through that one.

    Code (SQL):

    SELECT SUM(unit*sales)
    FROM article a,(SELECT * FROM mutations WHERE artno=1)m
    WHERE a.arrtno IN(m.artno,m.mutation);
     
    for artno=1 it gives 10*1+12*5(70) and if u give artno=2 then 1*12+12*7(96) as result
    Regards,
    Bharat G.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And to be precise,

    Code (SQL):

    SELECT M.ARTNO,SUM(UNIT*SALES)
      FROM ARTICLE A,
           MUTATIONS M
     WHERE A.ARRTNO IN(M.ARTNO,M.MUTATION)
     GROUP BY M.ARTNO;
     
     
    Bharat likes this.
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi raj,

    Thanks for reducing complexity in my query. I have written the query in a bit lengthy way.