Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Posts
    20

    Unanswered: Correlated SUB Queries?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •