Is this query correct the way it's written? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Is this query correct the way it's written?

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-08 : 14:15:05

I've joined the below queries which gives me results. I then added the natdocfile and want to make sure I am getting all of the results even if there are 0. Should I add the natdocfile twice or can I just add it to one of queries and still have both queries go against the natdocfile? I hope this makes sense.


select b.fo, pendinfo1, pendinfo2
from
(select t.fo, 
Isnull(COUNT(t.cossn), 0)AS PendinFO1 
     
FROM   t16pendall t 
       left join t16pendmvt m on t.cossn = m.cossn
right join natdocfile n on n.doc = t.fo
where mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y') and m.cossn is null
GROUP  BY t.fo)a

 join
       (SELECT t.fo, Isnull(COUNT(m.cossn), 0) AS pendinfo2 
       FROM   t16pendmvt m 
               JOIN t16pendall t 
                 ON m.cossn = t.cossn 
 right join natdocfile n on n.doc = t.fo 
     where ( mvt_typ = 'R' ) 
               AND not (mvt_loc LIKE 'R%' 
                       or mvt_loc LIKE 'S%' 
                     OR mvt_loc LIKE 'V%' ) 
        GROUP  BY t.fo)b
         ON a.fo = b.fo

Here are some of the results:

FO Pendinfo1 Pendinfo2
B19 392 10
704 338 1
317 204 2
399 624 13
856 1644 53

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-08 : 19:17:32

Giving it another try...

I have joined these two stored procedures together which work well. I now want to add one more table called the natdocfile. I want the two queries to run and output all of the values in the natdocfile even it it comes out to be a 0.

select b.fo, pendinfo1, pendinfo2
from
(select t.fo,
Isnull(COUNT(t.cossn), 0)AS PendinFO1

FROM t16pendall t
left join t16pendmvt m on t.cossn = m.cossn

where mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y') and m.cossn is null
GROUP BY t.fo)a

join
(SELECT t.fo, Isnull(COUNT(m.cossn), 0) AS pendinfo2
FROM t16pendmvt m
JOIN t16pendall t
ON m.cossn = t.cossn
where ( mvt_typ = 'R' )
AND not (mvt_loc LIKE 'R%'
or mvt_loc LIKE 'S%'
OR mvt_loc LIKE 'V%' )
GROUP BY t.fo)b
ON a.fo = b.fo



Here are the results from the above query 


doc         Pendinfo1     Pendinfo2
100             1            8
389             1            6
913 	        1            2 
A00	        5            3
A38             1            5 
B14             1            1



I want to add the natdocfile (right join natdocfile n on n.doc = t.fo)
and grab all of the doc's that are in the natdocfile even if 0 has to be added.  How do I add that right join to the query above? 


doc         PendinFO1     PendinFO2
001	        0             0
009             0             0
00K		0             0
00N		0 	      0 
00U		0             0
100		1             8
389		1             6
913		1	      2
A00		5             3
A38		1             5
B14		1             1

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-08 : 19:26:20

Looks like I could use a left or right outer join but don't know where to put it?

left/right outer join natdocfile n on n.doc = t.fo????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-09 : 00:46:15

Post some proper sample data and your expected result based on the supplied sample data.

N 56°04'39.26"
E 12°55'05.63"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-09 : 08:05:50

Thanks Peso I figured it out I will put all of the data into a temp table and then do a right join on the natdocfile to get what I need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources