trying to combine two separate queries in one

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

SQL Server 2000 Forums

Transact-SQL (2000)

trying to combine two separate queries in one

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-03-23 : 11:07:16

I have two queries, that do almost the same thing, with the difference of only one condition field. here they are:

in this one I use ftoso


select inmast.fpartno, sum(abs(intran.fqty))ann_usage_so
from inmast left outer join intran
on inmast.fpartno = intran.fpartno 
and intran.ftoso != ' '  
and intran.fdate >= '2001-11-01 00:00:00.000'
and intran.fdate <= '2004-11-01 00:00:00.000'
and intran.ftype = 'I'
where inmast.fpartno like 'pow%' 
group by inmast.fpartno
order by inmast.fpartno

in this one I use ftojob instead of ftoso


select inmast.fpartno, sum(abs(intran.fqty))ann_usage_jo
from inmast left outer join intran
on inmast.fpartno = intran.fpartno 
and intran.ftojob != ' '  
and intran.fdate >= '2001-11-01 00:00:00.000'
and intran.fdate <= '2004-11-01 00:00:00.000'
and intran.ftype = 'I'
where inmast.fpartno like 'pow%' 
group by inmast.fpartno
order by inmast.fpartno

What I would like to do is combine these two so that I can see the results for SO and JOB side by side. How can I do that?

thanx

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-23 : 11:13:24

This may not be good for performance:
...
on inmast.fpartno = intran.fpartno
and (intran.ftoso != ' ' OR intran.ftojob != ' ')
and intran.fdate >= '2001-11-01 00:00:00.000'
...

So maybe instead just UNION ALL the 2 statements

Be One with the Optimizer
TG

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-03-23 : 11:51:10

union is probably the way to go, but I keep getting syntax errors. could you please show me how the query should look like then? thank you very much

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-03-23 : 11:55:17

this is the query as I have it now:

select inmast.fpartno, inmast.fdescript, inmast.fstdcost, inmast.fonhand, inmast.fmeasure, inmast.flocate1,sum(abs(intran.fqty))ann_usage_so 
from inmast left outer join intran
on inmast.fpartno = intran.fpartno 
and intran.ftoso != ' '  
and intran.fdate >= '2001-11-01 00:00:00.000'
and intran.fdate <= '2004-11-01 00:00:00.000'
and intran.ftype = 'I'
where inmast.fpartno like 'pow%' 
group by inmast.fpartno, inmast.fdescript, inmast.fstdcost, inmast.fonhand, inmast.fmeasure, inmast.flocate1
order by inmast.fpartno 
UNION 
select inmast.fpartno, inmast.fdescript, inmast.fstdcost, inmast.fonhand, inmast.fmeasure, inmast.flocate1,sum(abs(intran.fqty))ann_usage_job
from inmast left outer join intran
on inmast.fpartno = intran.fpartno 
and intran.ftojob != ' '  
and intran.fdate >= '2001-11-01 00:00:00.000'
and intran.fdate <= '2004-11-01 00:00:00.000'
and intran.ftype = 'I'
where inmast.fpartno like 'pow%' 
group by inmast.fpartno, inmast.fdescript, inmast.fstdcost, inmast.fonhand, inmast.fmeasure, inmast.flocate1
order by inmast.fpartno

here is the error:

Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'UNION'.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-23 : 12:42:11

You only want the ORDER BY clause once (at the end of the statement). So comment out the first ORDER BY

Be One with the Optimizer
TG

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-03-23 : 17:52:19

yes I found that out after doing a bit more research, but unfortunately I think the union is not the way to go.
See i need the COLUMNS side by side rather than ROWS from two queries in one column.

so I think I need to figure out how to make LEFT OUTER JOIN work with these two. If you have any ideas, please let me know.
Thanx

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-03-24 : 08:59:48

Something like:


SELECT so.fpartno, so.ann_usage_so, job.ann_usage_jo
FROM
    (select inmast.fpartno, sum(abs(intran.fqty))ann_usage_so
    from inmast left outer join intran
         on inmast.fpartno = intran.fpartno 
         and intran.ftoso != ' '  
         and intran.fdate >= '2001-11-01 00:00:00.000'
         and intran.fdate <= '2004-11-01 00:00:00.000'
         and intran.ftype = 'I'
    where inmast.fpartno like 'pow%' 
    group by inmast.fpartno) so 
  LEFT OUTER JOIN 
    (select inmast.fpartno, sum(abs(intran.fqty))ann_usage_jo
     from inmast left outer join intran
          on inmast.fpartno = intran.fpartno 
          and intran.ftojob != ' '  
          and intran.fdate >= '2001-11-01 00:00:00.000'
          and intran.fdate <= '2004-11-01 00:00:00.000'
          and intran.ftype = 'I'
     where inmast.fpartno like 'pow%' 
     group by inmast.fpartno) job
   ON so.fpartno = job.fpartno
order by so.fpartno

Maybe?

~Travis

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-03-24 : 09:13:18

my friend travis, I thank you much. This has worked and now I have an actual example to learn from

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources