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

    Unanswered: get 5 rows from 2 unrelated tables (was "Complicated sql query.... need help badly")

    I am trying to write a query that queries 2 tables and gets back 5 rows. These two tables arent really related and the data I get back is different for either table.
    The 5 rows that I want to get back are for the LATEST 5 DATES. The date field for one table (F_INSPECTIONS) is END_DATE and the date field for the other table (F_OCCURRENCES) is OCCURRRENCE_DATE.

    I am writing a program to do this so if its absolutely impossible to implement this with sql code then a suggestion of how I might be able to go about doing it antoher way would help.

    Table descriptions:

    /****** Object: Table [dbo].[F_INSPECTIONS] Script Date: 2/8/2005 10:59:41 AM ******/
    CREATE TABLE [dbo].[F_INSPECTIONS] (
    [INSPECTION_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL ,
    [INSPECTION_NAME] [varchar] (150) COLLATE SQL_Latin1_General_CP850_CI_AI NULL ,
    [CAP_FACILITY_ID] [int] NOT NULL ,
    [REG_SURR_ID] [smallint] NOT NULL ,
    [START_DATE] [datetime] NULL ,
    [END_DATE] [datetime] NULL ,
    [INSP_UPDATED_ON] [datetime] NULL ,
    [INSP_ORIGIN_ID] [tinyint] NULL ,
    [INSP_TYPE_ID] [tinyint] NULL ,
    [DAYS_SINCE_LAST] [smallint] NULL ,
    [VIOLATION_COUNT] [smallint] NULL ,
    [NON_COMPLIANCE_IND] [tinyint] NULL ,
    [INSPECTION_COUNT] [smallint] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[F_OCCURRENCES] (
    [OCCURRENCE_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL ,
    [CAP_FACILITY_ID] [int] NOT NULL ,
    [OCCURRENCE_NM] [varchar] (150) COLLATE SQL_Latin1_General_CP850_CI_AI NULL ,
    [OCCURRENCE_DATE] [datetime] NULL ,
    [REG_SURR_ID] [smallint] NOT NULL ,
    [REPORTED_DATE] [datetime] NULL ,
    [ASSESSMENT_DATE] [datetime] NULL ,
    [UPDATED_ON] [datetime] NULL ,
    [ORIGIN_ID] [tinyint] NULL ,
    [CATEGORY_ID] [tinyint] NULL ,
    [OUTCOME_ID] [tinyint] NULL
    ) ON [PRIMARY]

    I need to query from these 2 tables and get these columns back:

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    Are you trying to get back only 5 rows(attempting to join 2 unrelated tables), or 5 rows from each table(candidate for a UNION)?

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If I understood well, you'll need something like this: first "join" similar results from both tables using the UNION, and then select first five of them.
    Code:
    SELECT   ID, dat
        FROM (SELECT   ins_id ID, end_date dat
              FROM F_INS
              UNION
              SELECT   occ_id ID, occ_date dat
              FROM F_OCC
              ORDER BY 2)
       WHERE ROWNUM <= 5
    ORDER BY 2;
    Selecting "top 5" rows can be done in different ways; this one (using the ROWNUM pseudocolumn) is used in Oracle. I *think* MySQL has "SELECT TOP 5 ..." statement; I don't know which database engine you use (is it Access?), but I hope my example will help you find the result.

  4. #4
    Join Date
    Feb 2005
    Posts
    20
    This is exacly the logic I was looking for (top 5 dates out of the entire set) Hopefully a union like this works in SQL SERVER.

    Thanks so much.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My translation of the Oracle syntax into SQL-92 (which should run on MS-SQL 2000) would be:
    Code:
    SELECT TOP 5 ID, dat
       FROM (
          SELECT   ins_id ID, end_date dat
             FROM F_INS
          UNION SELECT occ_id ID, occ_date dat
             FROM F_OCC) AS A
       ORDER BY 2
    -PatP

  6. #6
    Join Date
    Feb 2005
    Posts
    20
    Here is the statement I wrote using that same logic. It works great however. i cant get the top 5 to work.

    (select
    F_OCCURRENCES.CAP_FACILITY_ID,
    F_OCCURRENCES.REG_SURR_ID as reg_surr_id,
    'N/A' as INPECTOR,
    'N/A' as COMPLIANCE_STATUS,
    'OCCURRENCE' as ACTIVITY,
    D_OCCURRENCE_OUTCOME.OUTCOME_ENG_DESC as OUTCOME,
    D_REGULATION.REG_ENGLISH_DESC AS REGULATION,
    F_OCCURRENCES.OCCURRENCE_DATE as theDATE
    from F_OCCURRENCES INNER JOIN D_OCCURRENCE_OUTCOME ON F_OCCURRENCES.OUTCOME_ID = D_OCCURRENCE_OUTCOME.OUTCOME_ID INNER JOIN D_REGULATION ON D_REGULATION.REG_SURR_ID = F_OCCURRENCES.REG_SURR_ID where cap_facility_id = '11518' and F_OCCURRENCES.REG_SURR_ID = '101'
    UNION
    SELECT
    F_INSPECTIONS.CAP_FACILITY_ID,
    F_INSPECTIONS.REG_SURR_ID as reg_surr_id,
    'NO DATA' as INSPECTOR,
    CASE Non_COMPLIANCE_IND WHEN 1 THEN 'Non-Compliant' WHEN 0 THEN 'Compliant' END as COMPLIANCE_STATUS,
    'INSPECTION' as ACTIVITY,
    DISP_ENGLISH_DESC as OUTCOME,
    D_REGULATION.REG_ENGLISH_DESC AS REGULATION,
    F_INSPECTIONS.START_DATE as theDATE
    FROM F_INSPECTIONS LEFT JOIN F_VIOLATIONS ON F_INSPECTIONS.INSPECTION_ID = F_VIOLATIONS.INSPECTION_ID Left Join D_DISPOSITION on D_DISPOSITION.VIOLATION_DISP_ID = F_VIOLATIONS.VIOLATION_DISP_ID LEFT JOIN D_REGULATION ON F_INSPECTIONS.REG_SURR_ID = D_REGULATION.REG_SURR_ID where F_INSPECTIONS.REG_SURR_ID = '101' and F_INSPECTIONS.CAP_FACILITY_ID = '11518'
    )
    order by theDate desc
    Last edited by dbenoit64; 02-10-05 at 08:49.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is just a "shot in the dark" since I'm not willing to take the time to analyze this SQL at the moment, but if that SQL works then I'd suggest:
    Code:
    SELECT TOP 5 *
       FROM (
          SELECT
             F_OCCURRENCES.CAP_FACILITY_ID
    ,        F_OCCURRENCES.REG_SURR_ID as reg_surr_id
    ,        'N/A' as INPECTOR
    ,        'N/A' as COMPLIANCE_STATUS
    ,        'OCCURRENCE' as ACTIVITY
    ,        D_OCCURRENCE_OUTCOME.OUTCOME_ENG_DESC as OUTCOME
    ,        D_REGULATION.REG_ENGLISH_DESC AS REGULATION
    ,        F_OCCURRENCES.OCCURRENCE_DATE as theDATE
          FROM F_OCCURRENCES
          INNER JOIN D_OCCURRENCE_OUTCOME
             ON F_OCCURRENCES.OUTCOME_ID = D_OCCURRENCE_OUTCOME.OUTCOME_ID
          INNER JOIN D_REGULATION
             ON D_REGULATION.REG_SURR_ID = F_OCCURRENCES.REG_SURR_ID
          WHERE  cap_facility_id = '11518'
             and F_OCCURRENCES.REG_SURR_ID = '101' 
          UNION SELECT 
             F_INSPECTIONS.CAP_FACILITY_ID
    ,        F_INSPECTIONS.REG_SURR_ID as reg_surr_id
    ,        'NO DATA' as INSPECTOR
    ,        CASE Non_COMPLIANCE_IND
                WHEN 1 THEN 'Non-Compliant'
                WHEN 0 THEN 'Compliant'
             END as COMPLIANCE_STATUS
    ,        'INSPECTION' as ACTIVITY
    ,        DISP_ENGLISH_DESC as OUTCOME
    ,        D_REGULATION.REG_ENGLISH_DESC AS REGULATION
    ,        F_INSPECTIONS.START_DATE as theDATE
          FROM F_INSPECTIONS
          LEFT JOIN F_VIOLATIONS
             ON F_INSPECTIONS.INSPECTION_ID = F_VIOLATIONS.INSPECTION_ID
          LEFT JOIN D_DISPOSITION
             on D_DISPOSITION.VIOLATION_DISP_ID = F_VIOLATIONS.VIOLATION_DISP_ID
          LEFT JOIN D_REGULATION
             ON F_INSPECTIONS.REG_SURR_ID = D_REGULATION.REG_SURR_ID
          where  F_INSPECTIONS.REG_SURR_ID = '101'
             and F_INSPECTIONS.CAP_FACILITY_ID = '11518'
       ) AS z
       ORDER BY theDate desc
    -PatP

  8. #8
    Join Date
    Feb 2005
    Posts
    20
    Brilliant!
    the "as z" is what made this work.

    thanks very much

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That need for an alias bytes a lot of people... Standard SQL-92 isn't always as forgiving as Oracle (or any other vendor) can be, but that's the price you pay for portability.

    -PatP

Posting Permissions

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