Results 1 to 2 of 2

Thread: Complicated sql query.... need help badly

  1. #1
    Join Date
    Feb 2005
    Posts
    6

    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:
    Date, (F_inspections.end_date or F_OCCURRENCES.OCCURRENCE_DATE)
    Regulation: (F_INSPECTIONS.REG_SURR_ID or F_OCCURRENCES.REG_SURR_ID)
    Activity: Weather its an inspection or occurance (i can do this in code)
    Inspector: if its an inspection (i can do this in code probably)
    Outcome: if its an inspection and there was a problem then get what was done from another table....

    However, even if i can just get the Inspection_ids and Occurence ids and the type of id i can requery the data using the ids now that i know which ones are the latest (since I would have to requery anyways to get Regulation description rather than reg_surr_id and do a join to get Inspector_name as well as outcome.

  2. #2
    Join Date
    Feb 2005
    Posts
    7
    You can use "Union"

    select <<list cols>> from tab1

    Union

    select <<list cols>> from tab2


    Only thing is in both select sttaments number of columns and datatype should be same.

Posting Permissions

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