Nested Queries help - 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

Nested Queries help

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-04-26 : 09:22:42

This query works fine:


select t.doc, ISNULL(COUNT(t.clm), 0) as PendInPSC 
from
(select max(juris_mvt_seq_num) as juris_mvt_seq_num, clm, ppclms, org_id, juris_mvt_typ 
 From t2pendjuris j 
group by clm, clmssn,org_id, juris_mvt_typ)b
left join 
t2dibpend t on   b.clm=t.clm
where t.clm not in (select clm from people 
where completedt is null )and juris_mvt_typ='r'
and org_id like'P%' 
group by t.doc, t.clm, t.ppclms, org_id

What I need help with is how to add this select statement to the above query:


(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm 
from t2dibpend t
group by clm) f

I tried to add it as such:


select t.doc, ISNULL(COUNT(t.clm), 0) as PendInPSC 
from
(select max(juris_mvt_seq_num) as juris_mvt_seq_num, clm, ppclms, org_id, juris_mvt_typ 
 From t2pendjuris j 
group by clm)b
join
(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm 
from t2dibpend t
group by clm) f
left join 
t2dibpend t on   b.clm=t.clm and f.clm= t.clm
where t.cossn not in (select clm from people 
where completedt is null )and juris_mvt_typ='r'and org_id like'P%' 
group by t.doc, t.clm, t.ppclms, org_id

But I got these errors where the above is bold:

Incorrect syntax near the keyword 'where'
Incorrect syntax near the keyword 'and'

What am I doing wrong?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-26 : 09:25:42

no on clause for the f table.
Maybe it should be a cross join if you want a cartesian product

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-26 : 09:34:30

try this
select t.doc, ISNULL(COUNT(t.clm), 0) as PendInPSC
from
(select max(juris_mvt_seq_num) as juris_mvt_seq_num, clm, ppclms, org_id, juris_mvt_typ
From t2pendjuris j
group by clm)b
join
(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm
from t2dibpend t
group by clm) f
on f.clm = b.clm
left join
t2dibpend t
on b.clm=t.clm
where t.cossn not in (select clm from people where completedt is null)
and juris_mvt_typ='r'and org_id like'P%'
group by t.doc, t.clm, t.ppclms, org_id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-04-26 : 09:37:17

I thought this was it:

t2dibpend t on b.cossn=t.cossn and f.cossn= t.cossn

How would I add the on clause or do I join the t2pendjuris and t2dibpend tables?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-04-26 : 09:43:21

Thanks, I tried it but I'm getting

Msg 8120, Level 16, State 1, Line 1
Column 't2pendjuris.ppclms' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

When I click on the error it takes me to the top select statement:

select t.doc, ISNULL(COUNT(t.clm), 0) as PendInPSC

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-04-26 : 09:45:47

Never mind I got it!!!

I had to add ppclms to the first group by clause!

So I see you joined the two max statements then continued on with the left join. Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources