Correlated Sub Query? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Correlated Sub Query?

Author

Topic

dbenoit64
Starting Member

36 Posts

Posted - 2005-02-10 : 14:34:40

I would like to combine the following 3 select statements:

1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')

2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'

3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'

the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).

the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).

The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)

Thanks a lot,

Dave Benoit

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-10 : 14:52:10

you don't specify the join to F_VIOLATIONS - I've assumed REG_SURR_ID


select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID, case when  v.REG_SURR_ID is null then 'Compliant' else 'Not Compliant' end
from D_SECTION 
	INNER JOIN D_REGULATION 
		on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID 
	left outer join select distinct REG_SURR_ID from F_VIOLATIONS where SECTION_SURR_ID = '201') v
		on v.REG_SURR_ID = D_SECTION.REG_SURR_ID
		
where D_SECTION.reg_surr_id in ('101')

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

dbenoit64
Starting Member

36 Posts

Posted - 2005-02-11 : 07:19:38

Im sorry, I must have not been clear with my question.
The section

select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'

should be for the current SECTION_SURR_ID (not 201 every time). That was my biggest problem.. how do i get the current section_surr_id from the main query...

thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources