How to use the sub-query in main query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I have two table 1)Purchase(Name- TBL_Pur_Invdet) 2)Sale(Name- TBL_Sale_Invdet)

i want to show the total Purchase Qty, sale Qty, Purchase Return, Sale Return in a single query.

I use the SQL server 2008 R2
pLEASE HELP ME......

What I have tried:

Main Query---

SQL

select   c.catnm,i.itemnm,i.opnstk,u.untnm,(i.ITMcgst*2) as StkGSTRate from tbl_item as i
INNER JOIN tblcat  AS c   ON c.catid=i.ITMcatid    
INNER JOIN tblsubcat  AS sc   ON sc.subcatid=i.ITMsubcatid 
INNER JOIN tblUnit  AS u   ON u.untid=i.ITMcatid   
INNER JOIN TBL_pur_invdet  AS pd   ON pd.ItemIDpur =i.itemid

Sub-query
select distinct ItemIDpur,sum(PDQty )As purchaseQty,p_type  FROM TBL_pur_invdet where p_type='P' group by ItemIDpur,p_type
union all
select distinct ItemIDsale,sum(sDQty )As SaleQty,S_type  FROM TBL_sale_invdet where S_type='S' group by ItemIDsale,S_type
UNION ALL
select distinct ItemIDpur,sum(PDQty ) As purchaseReturnQty,p_type  FROM TBL_pur_invdet where p_type='PR' group by ItemIDpur,p_type
UNION ALL
select distinct ItemIDsale,sum(sDQty )As SaleReturnQty,S_type  FROM TBL_sale_invdet where S_type='SR' group by ItemIDsale,S_type

Posted 26-Feb-18 22:00pm

Jayanta Modak

Updated 27-Feb-18 0:41am

v2

Add a Solution

Comments

Santosh kumar Pithani 27-Feb-18 4:23am

please share Data records and expected output so its easy to give answer

Jayanta Modak 27-Feb-18 5:03am

https://ibb.co/bYyEvx

How to add the sub query in main query
i write the sub query for checking it is work or not
yes it is work but wont to add the sub-query into main query means when i run the main query i see all field in result

Jayanta Modak 27-Feb-18 6:19am

sorry! data record means table details ?

Suvendu Shekhar Giri 27-Feb-18 4:59am

You haven't mentioned about the problem, this is just requirement.

Jayanta Modak 27-Feb-18 5:03am

https://ibb.co/bYyEvx

How to add the sub query in main query
i write the sub query for checking it is work or not
yes it is work but wont to add the sub-query into main query means when i run the main query i see all field in result

Jayanta Modak 27-Feb-18 6:20am

I don't know how to add the sub-query i am beginner in sql language please help me

Santosh kumar Pithani 27-Feb-18 6:28am

your not mensioned any relation between two queries but i advice you try to use scalar sub queries.

Jayanta Modak 27-Feb-18 6:45am

sir please forgive me, i don't know how to join or relation between two query please help me please share some example or help please sir

1 solution

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)

Santosh kumar Pithani · Answer 1 · 2018-02-27T00:42:00

--i don't know relation but i have tried for OP 
select   c.catnm,i.itemnm,i.opnstk,u.untnm,(i.ITMcgst*2) as StkGSTRate,
(select sum(PDQty )As purchaseQty
      FROM TBL_pur_invdet T where p_type='P' AND 
             T.ItemIDpur=pd.ItemIDpur  group by ItemIDpur,p_type) AS purchasequantity,
(select sum(sDQty )As SaleQty 
     FROM TBL_sale_invdet T where S_type='S' AND 
             T.ItemIDsale=pd.ItemIDpur   group by ItemIDsale,S_type) AS salesQty,
(select sum(PDQty ) As purchaseReturnQty  
    FROM TBL_pur_invdet T  where p_type='PR' AND T.ItemIDpur=pd.ItemIDpur group by 
                                                ItemIDpur,p_type)AS PurchaseReturns,
(select sum(sDQty )As SaleReturnQty
    FROM TBL_sale_invdet T where S_type='SR' AND T.ItemIDsale=pd.ItemIDpur group by 
                                                 ItemIDsale,S_type) AS saleReturns
 from tbl_item as i
INNER JOIN tblcat  AS c   ON c.catid=i.ITMcatid    
INNER JOIN tblsubcat  AS sc   ON sc.subcatid=i.ITMsubcatid 
INNER JOIN tblUnit  AS u   ON u.untid=i.ITMcatid   
INNER JOIN TBL_pur_invdet  AS pd   ON pd.ItemIDpur =i.itemid