Combining 2 queries? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining 2 queries?

Author

Topic

Stev3n
Starting Member

1 Post

Posted - 2013-10-16 : 14:00:55

I have two queries, but I’m unsure exactly how to combine them. My guess is that I need a temp table, but I want your input. The first section (below) pulls all open orders for a specific type of lumber (based on specific attributes):

select sodID as 'Order ID',
prdDescrip as 'Product Description',
SUM(tagvolume) as 'Volume'
from SalesmanMaster
join OrderHeader on sohSalesmanID=salId
join OrderDetail on sohID=sodID
join CustomerMaster on sohCustID=cmId
join ProductMaster on prdID=sodProductID
join TagProdProfileMaster on prdID=tagProductID

/* ENTER THE PRODUCT ATTRIBUTES BELOW THIS LINE */

where prdThickID='04'
and prdGradeID='1c'
and prdUsrAttribID in ('rq')
and prdSpeciesID in ('wok')
and tagStatus='inventory'
and prdStateID<>'LG'
and tagLocationID LIKE 'MD%'
and sohStatusID='open'

group by sodID, prdDescrip
order by prdDescrip ASC;

The second part of the code pulls everything else that is needed to fulfill a specific order:

select tagID as 'Tag',
tagBinNo as 'Position',
tagLocationId as 'Location',
prdDescrip as 'Product Description',
SUM(tagvolume) as 'Volume'
from TagProdProfileMaster
join ProductMaster on prdID=tagProductID join OrderDetail on prdID=sodProductID join OrderHeader on sodID=sohID

/* ENTER THE ORDER NUMBER BELOW */

where sodID='md3570'

/* ENTER THE ORDER NUMBER ABOVE */

and tagStatus='inventory' and prdStateID<>'LG' and tagLocationID LIKE 'MD%' and sohStatusID='open'

group by tagBinNo, tagID, tagLocationId, prdDescrip
order by tagBinNo, prdDescrip, tagLocationId ASC;

How would I join these two together so that I could search for all orders where a specific type of board is being purchased AND see what else they're buying?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-10-16 : 18:49:41

You don't specify how your output should look like, so in these queries, I'm just guessing.
This query joins all your specified tables, so you can pull out the fields you need (no summarizing on volume).


select *
  from (select a.sohid
          from orderheader as a
               inner join orderdetail as b
                       on b.sodid=a.sohid
               inner join productmaster as c
                       on c.prdid=b.sodproductid
                      and c.prdstateid<>'LG'
                      and c.prdthickid='04'
                      and c.prdgradeid='1c'
                      and c.prdusrattribid='rq'
                      and c.prdspeciesid='wok'
               inner join tagprodprofilemaster as d
                       on d.tagproductid=b.sodproductid
                      and d.tagstatus='inventory'
                      and d.taglocation like 'MD%'
         where a.sohstatusid='open'
         group by a.sohid
       ) as a
       inner join orderheader as b
               on b.sohid=a.sohid
       inner join orderdetail as c
               on c.sohid=b.sohid
       inner join productmaster as d
               on d.prdid=c.sodproductid
       inner join tagprodprofilemaster as e
               on e.tagproductid=c.sodproductid
       inner join salesmanmaster as f
               on f.salid=b.sohsalesmanid
       inner join customermaster as g
               on g.cmid=b.sohcustid

Now this query shows same fields as in your queries, and summarizes volume per order, tag, position, location and product description.


select b.sohid as 'order id'
      ,d.tagid as tag
      ,d.tagbinno as position
      ,d.taglocationid as location'
      ,c.prddescrip as 'product description'
      ,sum(d.tagvolume) as volume
  from (select a.sohid
          from orderheader as a
               inner join orderdetail as b
                       on b.sodid=a.sohid
               inner join productmaster as c
                       on c.prdid=b.sodproductid
                      and c.prdstateid<>'LG'
                      and c.prdthickid='04'
                      and c.prdgradeid='1c'
                      and c.prdusrattribid='rq'
                      and c.prdspeciesid='wok'
               inner join tagprodprofilemaster as d
                       on d.tagproductid=b.sodproductid
                      and d.tagstatus='inventory'
                      and d.taglocation like 'MD%'
         where a.sohstatusid='open'
         group by a.sohid
       ) as a
       inner join orderdetail as b
               on b.sodid=a.sohid
       inner join productmaster as c
               on c.prdid=b.sodproductid
       inner join tagprodprofilemaster as d
               on d.tagproductid=b.sodproductid
 group by b.sohid
         ,d.tagid
         ,d.tagbinno
         ,d.taglocationid
         ,c.prddescrip

I should mention that the above queries show all orderlines for orders, where a specific type of board is being purchased. If you want to show all orders for all customers, buying a specific type of board, use the queries below. First the one with all tables/fields.


select *
  from (select a.sohcustid
          from orderheader as a
               inner join orderdetail as b
                       on b.sodid=a.sohid
               inner join productmaster as c
                       on c.prdid=b.sodproductid
                      and c.prdstateid<>'LG'
                      and c.prdthickid='04'
                      and c.prdgradeid='1c'
                      and c.prdusrattribid='rq'
                      and c.prdspeciesid='wok'
               inner join tagprodprofilemaster as d
                       on d.tagproductid=b.sodproductid
                      and d.tagstatus='inventory'
                      and d.taglocation like 'MD%'
         where a.sohstatusid='open'
         group by a.sohcustid
       ) as a
       inner join orderheader as b
               on b.sohcustid=a.sohcustid
       inner join orderdetail as c
               on c.sohid=b.sodid
       inner join productmaster as d
               on d.prdid=c.sodproductid
       inner join tagprodprofilemaster as e
               on e.tagproductid=c.sodproductid
       inner join salesmanmaster as f
               on f.salid=b.sohsalesmanid
       inner join customermaster as g
               on g.cmid=b.sohcustid

And the one with summarize.


select b.sohid as 'order id'
      ,d.tagid as tag
      ,d.tagbinno as position
      ,d.taglocationid as location'
      ,c.prddescrip as 'product description'
      ,sum(d.tagvolume) as volume
  from (select a.sohcustid
          from orderheader as a
               inner join orderdetail as b
                       on b.sodid=a.sohid
               inner join productmaster as c
                       on c.prdid=b.sodproductid
                      and c.prdstateid<>'LG'
                      and c.prdthickid='04'
                      and c.prdgradeid='1c'
                      and c.prdusrattribid='rq'
                      and c.prdspeciesid='wok'
               inner join tagprodprofilemaster as d
                       on d.tagproductid=b.sodproductid
                      and d.tagstatus='inventory'
                      and d.taglocation like 'MD%'
         where a.sohstatusid='open'
         group by a.sohcustid
       ) as a
       inner join orderheader as b
               on b.sohcustid=a.sohcustid
       inner join orderdetail as c
               on c.sodid=b.sohid
       inner join productmaster as d
               on d.prdid=c.sodproductid
       inner join tagprodprofilemaster as e
               on e.tagproductid=c.sodproductid
 group by b.sohid
         ,d.tagid
         ,d.tagbinno
         ,d.taglocationid
         ,c.prddescrip

Hope this is somewhere near the result you are looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources