@Balboos has shown you how to create a view.
One of the reasons we are doubting you are using MS SQL is because of this code snippet (reformatted to make it readable)
FROM issue_details
INNER JOIN issue_reci_master ON issue_details.isno = issue_reci_master.isno
INNER JOIN gatein_details
INNER JOIN gatein_master ON gatein_details.gateno = gatein_master.gateno
INNER JOIN store_reiss
INNER JOIN store_reissdetails ON store_reiss.mirno = store_reissdetails.mirno
ON gatein_details.item_code = store_reissdetails.item_code
ON issue_details.item_code = store_reissdetails.item_code
INNER JOIN item_master ON issue_details.item_code = item_master.item_code
That's the sort of construct we would normally see in an MS Access query - along with a load of brackets. You also have
AS Expr8
which is straight out of MS Access. Finally, every column you retrieve has the table name qualifier - yet another feature of MS Access.
Keep your
ON
clauses tightly coupled to the
JOIN
FROM issue_details
INNER JOIN issue_reci_master ON issue_details.isno = issue_reci_master.isno
INNER JOIN store_reiss
INNER JOIN store_reissdetails ON store_reiss.mirno = store_reissdetails.mirno
AND issue_details.item_code = store_reissdetails.item_code
INNER JOIN item_master ON issue_details.item_code = item_master.item_code
INNER JOIN gatein_details ON gatein_details.item_code = store_reissdetails.item_code
INNER JOIN gatein_master ON gatein_details.gateno = gatein_master.gateno
That's when you can clearly see that you have no ON
clause for the join to store_reiss
- quite likely to be the cause of your "but showing all records" issue.
Learn about table aliases - they are a really good way of avoiding have to type long table names and making code a lot easier to read. Make use of newlines and whitespace too - not just to line things up but to make it easier to read … e.g. yours could become
SELECT SR.mirno, SR.mirenbyuser, SR.mirdate, SR.geno,
SR.genobyuser, SR.gedate, SR.supname,
SRD.rate, SR.chno, SR.chdate, GD.qtyinstpc,
GD.qtyinstpc * SR.rate AS Expr1,
GD.qtyinstwt,
GD.qtyinstwt * SRD.rate AS Expr2,
GD.challanno, GD.chdate AS Expr3,
GD.qty, GD.chqtyinwt,
GD.qty * SRD.rate AS Expr4,
GD.chqtyinwt * SRD.rate AS Expr5,
IRM.isno, IRM.[isdate], ID.issqtypcs, ID.issqtywt,
ID.issqtypcs * SRD.rate AS issamtpc,
ID.issqtywt * SRD.rate AS issamtwt,
GD.qtyinstpc + GD.qty - ID.issqtypcs AS Expr6,
GD.qtyinstwt + GD.chqtyinwt - ID.issqtywt AS Expr7,
(GD.qtyinstpc + GD.qty - ID.issqtypcs) * SR.rate AS Expr8,
(GD.qtyinstwt + GD.chqtyinwt - ID.issqtywt) * SRD.rate AS Expr9,
IM.item_desc, IM.item_code, IM.idesc, IM.unit, IM.maxlevelqty,
IM.minilevelqty, IM.rackno, IM.bincardno
INTO Store1
FROM issue_details ID
INNER JOIN issue_reci_master IRM ON ID.isno = IRM.isno
INNER JOIN store_reiss SR
INNER JOIN store_reissdetails SRD ON SR.mirno = SRD.mirno
AND ID.item_code = SRD.item_code
INNER JOIN item_master IM ON IM.item_code = ID.item_code
INNER JOIN gatein_details GD ON GD.item_code = SRD.item_code
INNER JOIN gatein_master GM ON GD.gateno = GM.gateno
WHERE SR.mirdate BETWEEN '04/01/2019' AND '08/23/2019'
ORDER BY SR.mirno