How do I group by multiple query and how do I make SQL view

Question

1.00/5 (1 vote)

See more:

SELECT     store_reiss.mirno, store_reiss.mirenbyuser, store_reiss.mirdate, store_reiss.geno, store_reiss.genobyuser, store_reiss.gedate, store_reiss.supname, 
                      store_reissdetails.rate, store_reiss.chno, store_reiss.chdate, gatein_details.qtyinstpc, gatein_details.qtyinstpc * store_reissdetails.rate AS Expr1, 
                      gatein_details.qtyinstwt, gatein_details.qtyinstwt * store_reissdetails.rate AS Expr2, gatein_details.challanno, gatein_details.chdate AS Expr3, gatein_details.qty, 
                      gatein_details.chqtyinwt, gatein_details.qty * store_reissdetails.rate AS Expr4, gatein_details.chqtyinwt * store_reissdetails.rate AS Expr5, issue_reci_master.isno, 
                      issue_reci_master.isdate, issue_details.issqtypcs, issue_details.issqtywt, issue_details.issqtypcs * store_reissdetails.rate AS issamtpc, 
                      issue_details.issqtywt * store_reissdetails.rate AS issamtwt, gatein_details.qtyinstpc + gatein_details.qty - issue_details.issqtypcs AS Expr6, 
                      gatein_details.qtyinstwt + gatein_details.chqtyinwt - issue_details.issqtywt AS Expr7, (gatein_details.qtyinstpc + gatein_details.qty - issue_details.issqtypcs) 
                      * store_reissdetails.rate AS Expr8, (gatein_details.qtyinstwt + gatein_details.chqtyinwt - issue_details.issqtywt) * store_reissdetails.rate AS Expr9, 
                      item_master.item_desc, item_master.item_code, item_master.idesc, item_master.unit, item_master.maxlevelqty, item_master.minilevelqty, item_master.rackno, 
                      item_master.bincardno
INTO            Store1
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
WHERE     (store_reiss.mirdate BETWEEN '04/01/2019' AND '08/23/2019')
ORDER BY store_reiss.mirno

What I have tried:

i got desired result from seven tables but showing all records and i have to make one view or store procedure to generate crystal report in vb.net ,please help me its urgent ...

Thanks

Posted 26-Aug-19 3:28am

princeharish21

Updated 29-Aug-19 6:41am

v2

Add a Solution

Comments

#realJSOP 26-Aug-19 13:57pm

What sql is that? As far as I can tell, that's gonna be invalid in MS Sql...

MadMyche 26-Aug-19 14:40pm

Ugly SQL

RmcbainTheThird 26-Aug-19 14:54pm

ms access sql

princeharish21 29-Aug-19 5:27am

sql server

Richard Deeming 27-Aug-19 12:19pm

1) No, it's not urgent. Not for anyone here. All you achieve by trying to boost the priority of your question like this is to tell us that you can't manage your own time.

2) There is nowhere near enough information in this question for anyone to help you. You haven't told us what you want to achieve, what you've tried, or where you're stuck. You haven't shown us the structure of your tables, or any sample data. You haven't shown us what output you're trying to generate, or what output you're currently getting.

3) That SQL query is unreadable.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2019-08-29T05:34:00

Solution 1

Here's a help on how to make a view (if you get that far) - for, as you claim, MS SQL:

SQL CREATE VIEW, REPLACE VIEW, DROP VIEW Statements[^]

Posted 29-Aug-19 5:34am

W∴ Balboos, GHB

Comments

Maciej Los 29-Aug-19 11:59am

Short And To The Point!

CHill60 · Answer 2 · 2019-08-29T06:41:00

@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)

SQL

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

SQL

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

SQL

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 -- Fix this ON clause
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