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

Query question

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-10 : 14:06:42

I was told it's not efficient to use the natdocfile twice in the two queries I've joined. How can I set up the queries to just use one natdocfile? I want to right join on the natdocfile to get all records in there to appear even if they are 0 for the value.

Here's my two queries


SELECT a.doc, pendinfo1, pendinfo2 

FROM 

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

join

(SELECT n.doc, 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 
and ( mvt_typ = 'R' ) AND not (mvt_loc LIKE 'R%' or mvt_loc LIKE 'S%' OR mvt_loc LIKE 'V%' ) 
GROUP BY n.doc)b
on a.doc = b.doc

Here's the table info:


CREATE TABLE [dbo].[T16pendall](
	[COSSN] [char](6) NOT NULL,
	[FLG_CDT] [datetime] NULL,
	[AGED_ALIEN_RSW] [char](1) NOT NULL,
	[MFT_POSN1_CD] [char](1) NOT NULL,
	[FO] [varchar](4) NOT NULL
) ON [PRIMARY]



insert into T16pendall
select '268763', '10/6/2010', ' ', 'D', '389' union all
select '337629', '10/21/2010', ' ', 'D', 'A00' union all
select '240312', '10/28/2010', ' ', 'D', '330' union all
select '406339', '10/4/2010', ' ', 'D', '442' union all
select '279700', '11/1/2010', ' ', 'D', '387' union all
select '680031', '10/29/2010', ' ', 'D', 'A00' union all
select '298582', '10/26/2010', ' ', 'D', '387' union all
select '074506', '9/23/2010', ' ', 'D', '329' union all
select '610128', '10/6/2010', ' ', 'A', '893' union all
select '427337', '6/30/2010', ' ', 'D', 'B14' union all
select '553084', '10/15/2010', ' ', 'D', '913' union all
select '587962', '8/30/2010', ' ', 'D', '100' union all
select '746855', '9/10/2010', ' ', 'D', 'A38' union all
select '429194', '9/13/2010', ' ', 'D', 'A00' union all
select '424431', '9/14/2010', ' ', 'D', 'A00' 

Second Table and Insert statement

CREATE TABLE [dbo].[T16pendmvt](
    [COSSN] [char](6) NOT NULL,
    [MVT_TYP] [char](1) NOT NULL,
    [MVT_LOC] [char](3) NOT NULL,
    [MVT_DEST] [varchar](3) NOT NULL,
    [MVT_CDT] [datetime] NULL
) ON [PRIMARY]

insert into T16pendmvt
select '074506', 'T', '329', 'S36', '9/23/2009' union all

select '610128', 'R', 'S15', '  ', '10/6/2010' union all

select '427337', 'R', 'S27', ' ', '7/1/2010' union all

select '553084', 'R', 'V64', ' ', '10/26/2010' union all

select '999962', 'T', 'SS2', 'R42', '2/3/2003' union all

select '452262', 'R', 'VS2', '   ', '1/22/2004' union all

select '458962', 'R', 'V36', '   ', '5/5/2004' union all

select '458962', 'R', 'V25', '858', '6/8/2004' union all

select '458962', 'T', 'S72', 'S24', '7/19/2004' union all

select '458962', 'R', '004', ' ', '4/8/2010'

Last table and insert statement

CREATE TABLE [dbo].[natdocfile](
[doc] [varchar](3) NOT NULL
) ON [PRIMARY]


insert into natdocfile
select '001' union all
select 'A00' union all
select '389' union all
select 'A38' union all
select 'B14' union all
select '913' union all
select '009' union all
select '00K' union all
select '00N' union all
select '00U'

Here are the results:


Doc  pendinfo1   pendinfo2    
001	0	     0
009	0	     0
00K	0	     0
00N	0	     0
00U	0	     0
389	1	     0
913	0	     0
A00	4	     0
A38	1	     0
B14	0	     0

As you can see all of the doc fields are listed from the Natdocfile which is what I want.

Is there another way to just list the one natdocfile at the end of the query or the top of it, I can't figure it out. Thanks!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-10 : 17:45:00

[code]
Select n.doc
,SUM(CASE WHEN (mft_POSN1_CD in ('b','d')
OR (mft_POSN1_CD = 'a' and aged_alien_rsw = 'y'))
THEN 1 else 0 end) as PendinFO1

,SUM(CASE WHEN mvt_typ = 'R' and LEFT (mvt_loc,1) not in ('R','S','V')
THEN 1 else 0 end) as PendinFO2
FROM natdocfile n
LEFT JOIN t16pendall t on n.doc = t.fo and t.cossn not in (Select cossn from t16pendmvt)
LEFT JOIN t16pendmvt m on t.cossn = m.cossn
GROUP BY n.doc
[/code]

Results
[code]
doc PendinFO1 PendinFO2
001 0 0
009 0 0
00K 0 0
00N 0 0
00U 0 0
389 1 0
913 0 0
A00 4 0
A38 1 0
B14 0 0
[/code]

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-10 : 20:17:10

Thanks so much dataguru! I have a couple of questions...can you explain the bold code of yours to me please.

I also have other queries so I would just add them in front of the from natdocfile n right?



Select n.doc
	,SUM(CASE WHEN (mft_POSN1_CD in ('b','d')
				OR (mft_POSN1_CD = 'a' and aged_alien_rsw = 'y'))
					  THEN 1 else 0 end) as PendinFO1

	,SUM(CASE WHEN mvt_typ = 'R' and LEFT (mvt_loc,1) not in ('R','S','V')
					  THEN 1 else 0 end) as PendinFO2
FROM natdocfile n 
LEFT JOIN t16pendall t	on n.doc = t.fo and t.cossn not in (Select cossn from t16pendmvt)
LEFT JOIN t16pendmvt m on t.cossn = m.cossn
GROUP BY n.doc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources