CODEWITHSUNDEEP
sql
remss
remss

Reputation: 45

how to use inner join queries for four tables

I have four tables in my database(notes, expense, category and items).

items table will have so many items on the particular category id items table have fields

id(primary key), item name(text), unit(text), category id(foreign key)

category table will have just two fields category table have fields

id(primary key), cat_id(integer), category name(text).

notes table is used to have a record for each shopping note. notes table have fields

note_id(primary key), date(DATE), total cost(integer).

expense table is used to store the items purchased and its corresponding quantity and price under the particular shopping note using note id as the foreign key expense table has fields

id(primary key), note_id(foreign key), quantity(integer), price(integer), item_name(text)

When I input the from date,to date and a particular category i need the items under that category that was purchaed between the from and to date. i need a query that will give output as:

ITEM NAME           TOTALQTY          TOTALPRICE
carrot                5kg                 500

can anyone help me with a solution??

Upvotes: 2

Views: 8121

Answers (1)

Phil
Phil

Reputation: 42991

Try this

select A.name, sum(C.unit), A.unit, sum(C.price) 
from items as A 
    INNER JOIN categories as B ON A.category=B._id
    INNER JOIN expenses as C ON A.name=C.item_name
    INNER JOIN notes as D ON C.Note_id=D._id
where 
    D.date1 between '2012-01-01' and '2012-03-31' and B.name='Vegetables'
group by
    A.name, A.unit

Upvotes: 3

Related Questions