Results 1 to 7 of 7
  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
    i'm not sure where the correlation comes in

    these are uncorrelated subqueries --
    Code:
    select SECTION_ENGLISH_DESC
         , D_REGULATION.REG_ENGLISH_DESC
         , D_SECTION.REG_SURR_ID 
         , ( select count(*) 
               from F_INSPECTIONS 
              where REG_SURR_ID = '101' ) as inspections
         , case when
           ( select count(*) 
               from F_INSPECTIONS 
              where SECTION_SURR_ID = '201' ) = 0
                 then   'Compliant'
                 else 'Not Compliant'  end  as compliancies
      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')
    if these really should be correlated, then you can add a WHERE condition to each subquery that references one of the outer tables (D_SECTION or D_REGULATION)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    to clarify
    these are not correlated subqueries because the inner query does not reference the outer query with an table alias.

  4. #4
    Join Date
    Feb 2005
    Posts
    20
    however, thats exactly what i need to do. In this section:

    select count(*)
    from F_INSPECTIONS
    where SECTION_SURR_ID = '201' ) = 0
    then 'Compliant'
    else 'Not Compliant' end as compliancies

    I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dbenoit64
    I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.
    yeah, that was the missing info, wasn't it
    Code:
    select SECTION_ENGLISH_DESC
         , D_REGULATION.REG_ENGLISH_DESC
         , D_SECTION.REG_SURR_ID 
         , ( select count(*) 
               from F_INSPECTIONS 
              where REG_SURR_ID 
                  = D_SECTION.REG_SURR_ID  ) as inspections
         , case when
           ( select count(*) 
               from F_INSPECTIONS 
              where SECTION_SURR_ID 
                  = D_SECTION.REG_SURR_ID ) = 0
                 then   'Compliant'
                 else 'Not Compliant'  end  as compliancies
      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')
    if that's not the right correlation, at least now you know how to do it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2005
    Posts
    20
    Hi, Thanks for the answer but im still a bit confused on that same section.

    The thing is that I have to find the current inspection number to see if there were any VIOLATIONS (if there was 1 or more VIOLATIONS!!, then its not compliant, otherwise it is considered compliant).

    NOTICE the alais currSecID I created in order to make a link between the current SECTION_ID (not regulation_ID) and the one used in the VIOLATIONS Compliant section (above)


    select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID,
    D_SECTION.SECTION_SURR_ID currSecID,
    ( select count(*)
    from F_INSPECTIONS
    where REG_SURR_ID
    = D_SECTION.REG_SURR_ID ) as inspections,
    case when
    ( select count(*)
    from F_VIOLATIONS
    where SECTION_SURR_ID = currSecID) = 0
    then 'Compliant'
    else 'Not Compliant' end as compliancies
    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')

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    don't use the alias in the subquery
    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
  •