Tharake Gunatilake

Tharake Gunatilake

  • NA
  • 53
  • 73.2k

Way of connecting tables

Mar 27 2013 1:33 AM
Friends i have a problem...

i have 3 tables...

CREATE DATABASE STORE_TEST;
USE STORE_TEST;

CREATE TABLE ITEM
(
item_id int,
item_name varchar(50),
available_qty int,
added_date datetime,

CONSTRAINT PK_ITEM PRIMARY KEY (item_id)
);

CREATE TABLE STOCK_IN
(
in_id int,
in_item_id int,
in_qty int,
in_date datetime,

CONSTRAINT PK_STOCK_IN PRIMARY KEY (in_id),
CONSTRAINT FK_STOCK_IN FOREIGN KEY (in_item_id) REFERENCES ITEM (item_id)
);

CREATE TABLE STOCK_OUT
(
out_id int,
out_item_id int,
out_qty int,
out_date datetime,

CONSTRAINT PK_STOCK_OUT PRIMARY KEY (out_id),
CONSTRAINT FK_STOCK_OUT FOREIGN KEY (out_item_id) REFERENCES ITEM (item_id)
);


i want to select item_id ,item_name, sum of out_qty,sum of in_qty and available_qty  
in a specified date period.
How can i do it..
Please help me..
Thank you

Answers (3)