6 Replies - 602 Views - Last Post: 28 February 2018 - 11:23 AM Rate Topic: -----

#1 [email protected]   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 90
  • Joined: 16-May 17

How to use the sub-query in main query

Posted 27 February 2018 - 02:31 AM

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

This post has been edited by [email protected]: 27 February 2018 - 02:34 AM


Is This A Good Question/Topic? 0
  • +

Replies To: How to use the sub-query in main query

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14925
  • View blog
  • Posts: 59,592
  • Joined: 12-June 08

Re: How to use the sub-query in main query

Posted 27 February 2018 - 08:07 AM

Seems like you can do 'sum' over the columns you want to sum in both tables.. group by the ID.. then join on that ID to get a single row.
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14925
  • View blog
  • Posts: 59,592
  • Joined: 12-June 08

Re: How to use the sub-query in main query

Posted 27 February 2018 - 08:14 AM

Example:

cre ate table #sales (id int, sval int, sval2 int)
cre ate table #re (id int, sval int, sval2 int)

insert into #sales(id, sval, sval2) values (1, 3, 5)
insert into #sales(id, sval, sval2) values (1, 5, 7)
insert into #sales(id, sval, sval2) values (1, 1, 2)
insert into #sales(id, sval, sval2) values (2, 1, 1)

insert into #re(id, sval, sval2) values (1, 3, 5)
insert into #re(id, sval, sval2) values (2, 5, 7)
insert into #re(id, sval, sval2) values (2, 1, 2)
insert into #re(id, sval, sval2) values (3, 1, 1)
insert into #re(id, sval, sval2) values (3, 5, 8)

select *
from (select id, sum(sval) as sumVal, sum(sval2) as sumVal2
from #sales
group by id) as a
 join (
select id, sum(sval) as sumVal, sum(sval2) as sumVal2
from #re
group by id) as b on a.id = b.id

dr op table #re
dr op table #sales


id          sumVal      sumVal2     id          sumVal      sumVal2
----------- ----------- ----------- ----------- ----------- -----------
1           9           14          1           3           5
2           1           1           2           6           9

Was This Post Helpful? 0
  • +
  • -

#4 baavgai   User is online

  • Dreaming Coder
  • member icon


Reputation: 7419
  • View blog
  • Posts: 15,374
  • Joined: 16-October 07

Re: How to use the sub-query in main query

Posted 27 February 2018 - 08:33 AM

First, your current select only uses three tables:
select  c.catnm, i.itemnm, i.opnstk, u.untnm, (i.ITMcgst*2) as StkGSTRate
  from tbl_item i
    JOIN tblcat c
      ON c.catid=i.ITMcatid
    JOIN tblUnit u
      ON u.untid=i.ITMcatid



Now, rather than a union...
select  c.catnm, i.itemnm, i.opnstk, u.untnm, (i.ITMcgst*2) as StkGSTRate,
    tp.qty as purchaseQty
  from tbl_item i
    JOIN tblcat c
      ON c.catid=i.ITMcatid
    JOIN tblUnit u
      ON u.untid=i.ITMcatid
    LEFT JOIN (select ItemIDpur as itemid, sum(PDQty) As qty FROM TBL_pur_invdet where p_type='P' group by ItemIDpur) tp
      ON i.itemid=tp.itemid



Outer joining the rest is up to you. You'll probably want IsNull for zeros. Also, a distinct is a group by screams bug: don't do that.
Was This Post Helpful? 1
  • +
  • -

#5 [email protected]   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 90
  • Joined: 16-May 17

Re: How to use the sub-query in main query

Posted 27 February 2018 - 11:37 AM

View Postbaavgai, on 27 February 2018 - 09:03 PM, said:

First, your current select only uses three tables:
select  c.catnm, i.itemnm, i.opnstk, u.untnm, (i.ITMcgst*2) as StkGSTRate
  from tbl_item i
    JOIN tblcat c
      ON c.catid=i.ITMcatid
    JOIN tblUnit u
      ON u.untid=i.ITMcatid



Now, rather than a union...
select  c.catnm, i.itemnm, i.opnstk, u.untnm, (i.ITMcgst*2) as StkGSTRate,
    tp.qty as purchaseQty
  from tbl_item i
    JOIN tblcat c
      ON c.catid=i.ITMcatid
    JOIN tblUnit u
      ON u.untid=i.ITMcatid
    LEFT JOIN (select ItemIDpur as itemid, sum(PDQty) As qty FROM TBL_pur_invdet where p_type='P' group by ItemIDpur) tp
      ON i.itemid=tp.itemid



Outer joining the rest is up to you. You'll probably want IsNull for zeros. Also, a distinct is a group by screams bug: don't do that.


Thanks for reply
Sir it is work fine but one BIG problem Result is ok when item under top 3 category id if item is under 4th category then the query not show the purticular item

I delete All category and drop table then re add the category , same as sub-category and item table please help me sir please....
Was This Post Helpful? 0
  • +
  • -

#6 [email protected]   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 90
  • Joined: 16-May 17

Re: How to use the sub-query in main query

Posted 27 February 2018 - 12:19 PM

SORRY! sir i do it i just change the this line :

JOIN tblUnit u   ON u.untid=i.ITMcatid



JOIN tblUnit u    ON u.untid=i.ITMunitid



Thanks sir
you are so great.
You are very intelligent and intelligence is god gifted
thanks
Was This Post Helpful? 0
  • +
  • -

#7 [email protected]   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 90
  • Joined: 16-May 17

Re: How to use the sub-query in main query

Posted 28 February 2018 - 11:23 AM

View Postbaavgai, on 27 February 2018 - 09:03 PM, said:

First, your current select only uses three tables:
select  c.catnm, i.itemnm, i.opnstk, u.untnm, (i.ITMcgst*2) as StkGSTRate
  from tbl_item i
    JOIN tblcat c
      ON c.catid=i.ITMcatid
    JOIN tblUnit u
      ON u.untid=i.ITMcatid



Now, rather than a union...
select  c.catnm, i.itemnm, i.opnstk, u.untnm, (i.ITMcgst*2) as StkGSTRate,
    tp.qty as purchaseQty
  from tbl_item i
    JOIN tblcat c
      ON c.catid=i.ITMcatid
    JOIN tblUnit u
      ON u.untid=i.ITMcatid
    LEFT JOIN (select ItemIDpur as itemid, sum(PDQty) As qty FROM TBL_pur_invdet where p_type='P' group by ItemIDpur) tp
      ON i.itemid=tp.itemid



Outer joining the rest is up to you. You'll probably want IsNull for zeros. Also, a distinct is a group by screams bug: don't do that.


Sir Zero showing is very Important in the place of NULL, because I want show the closing quantity (total purchase - total sale ) some product only purchase or sale quantity is available that for result not come properly. If sale and purchase is available then showing the closing quantity otherwise closing quantity show NULL
Please help me sir please
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1