Professional Web Applications Themes

join on 3 tables for asp output - ASP Database

I have three tables. The table structure and sample data is below(sorry for the length). I want a select that will retrieve all Mondays since I am scheduling instruments for a whole week. Every Instrument should have a row for every Monday that way I can show that it hasn't been scheduled. Thanks for any help you can provide Mike CREATE TABLE [dbo].[Tab_Inst_Schedules] ( [Instrument_ID] [int] NOT NULL , [WeekOf] [smalldatetime] NOT NULL ) ON [PRIMARY] GO Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 10/25/2004) Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 11/01/2004) Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, ...

  1. #1

    Default join on 3 tables for asp output

    I have three tables. The table structure and sample data is below(sorry for
    the length). I want a select that will retrieve all Mondays since I am
    scheduling instruments for a whole week. Every Instrument should have a row
    for every Monday that way I can show that it hasn't been scheduled.

    Thanks for any help you can provide

    Mike



    CREATE TABLE [dbo].[Tab_Inst_Schedules] (
    [Instrument_ID] [int] NOT NULL ,
    [WeekOf] [smalldatetime] NOT NULL
    ) ON [PRIMARY]
    GO
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 10/25/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 11/01/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 11/08/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 11/15/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 11/22/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 11/29/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 12/06/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 12/13/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 12/20/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 12/27/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 01/03/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 01/10/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 01/17/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 01/24/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 01/31/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 10/25/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 11/01/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 11/08/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 11/15/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 11/22/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 11/29/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 12/06/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 12/13/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 12/20/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 12/27/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 01/03/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 01/10/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 01/17/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 01/24/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 01/31/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 10/25/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 11/01/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 11/08/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 11/15/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 11/22/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 11/29/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 12/06/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 12/13/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 12/20/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 12/27/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 01/03/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 01/10/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 01/17/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 01/24/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 01/31/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 10/25/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 11/01/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 11/08/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 11/15/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 11/22/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 11/29/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 12/06/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 12/13/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 12/20/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 12/27/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 01/03/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 01/10/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 01/17/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 01/24/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 01/31/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 10/25/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 11/01/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 11/08/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 11/15/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 11/22/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 11/29/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 12/06/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 12/13/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 12/20/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 12/27/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 01/03/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 01/10/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 01/17/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 01/24/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 01/31/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 11/01/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 11/08/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 11/15/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 11/22/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 11/29/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 12/06/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 12/13/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 12/20/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 12/27/2004)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 01/03/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 01/10/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 01/17/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 01/24/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 01/31/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 02/07/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 02/14/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 02/21/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 02/28/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 03/07/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 03/14/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 03/21/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 03/28/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 04/04/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 04/11/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 04/18/2005)
    Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 04/25/2005)

    CREATE TABLE [dbo].[Tab_Instrument_Details] (
    [InstrumentID] [smallint] IDENTITY (1, 1) NOT NULL ,
    [InstrumentName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL ,
    [Active] [bit] NOT NULL
    ) ON [PRIMARY]
    GO

    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (1, 'SX01', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (2, 'SX02', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (3, 'SX03', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (4, 'SX04', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (5, 'SX05', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (6, 'SX06', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (7, 'SX07', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (8, 'SX08', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (9, 'SX09', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (10, 'SX10', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (11, 'SX11', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (12, 'SX12', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (13, 'SX13', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (14, 'SX14', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (15, 'SX15', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (16, 'MX01', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (17, 'MX02', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (18, 'MX03', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (19, 'MX04', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (20, 'MX05', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (21, 'MX05', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (22, 'MX06', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (23, 'MX07', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (24, 'MX08', TRUE)
    Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
    Values (25, 'MX09', TRUE)

    CREATE TABLE [dbo].[Tab_Lookup_Calendar] (
    [dt] [smalldatetime] NOT NULL ,
    [isWeekday] [bit] NULL ,
    [Y] [smallint] NULL ,
    [Q] [tinyint] NULL ,
    [M] [tinyint] NULL ,
    [D] [tinyint] NULL ,
    [DW] [tinyint] NULL
    ) ON [PRIMARY]
    GO

    Sample data:
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/02/2004', FALSE, 2004, 4, 10, 2, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/03/2004', FALSE, 2004, 4, 10, 3, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/04/2004', TRUE, 2004, 4, 10, 4, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/05/2004', TRUE, 2004, 4, 10, 5, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/06/2004', TRUE, 2004, 4, 10, 6, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/07/2004', TRUE, 2004, 4, 10, 7, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/08/2004', TRUE, 2004, 4, 10, 8, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/09/2004', FALSE, 2004, 4, 10, 9, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/10/2004', FALSE, 2004, 4, 10, 10, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/11/2004', TRUE, 2004, 4, 10, 11, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/12/2004', TRUE, 2004, 4, 10, 12, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/13/2004', TRUE, 2004, 4, 10, 13, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/14/2004', TRUE, 2004, 4, 10, 14, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/15/2004', TRUE, 2004, 4, 10, 15, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/16/2004', FALSE, 2004, 4, 10, 16, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/17/2004', FALSE, 2004, 4, 10, 17, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/18/2004', TRUE, 2004, 4, 10, 18, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/19/2004', TRUE, 2004, 4, 10, 19, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/20/2004', TRUE, 2004, 4, 10, 20, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/21/2004', TRUE, 2004, 4, 10, 21, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/22/2004', TRUE, 2004, 4, 10, 22, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/23/2004', FALSE, 2004, 4, 10, 23, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/24/2004', FALSE, 2004, 4, 10, 24, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/25/2004', TRUE, 2004, 4, 10, 25, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/26/2004', TRUE, 2004, 4, 10, 26, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/27/2004', TRUE, 2004, 4, 10, 27, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/28/2004', TRUE, 2004, 4, 10, 28, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/29/2004', TRUE, 2004, 4, 10, 29, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/30/2004', FALSE, 2004, 4, 10, 30, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('10/31/2004', FALSE, 2004, 4, 10, 31, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/01/2004', TRUE, 2004, 4, 11, 1, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/02/2004', TRUE, 2004, 4, 11, 2, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/03/2004', TRUE, 2004, 4, 11, 3, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/04/2004', TRUE, 2004, 4, 11, 4, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/05/2004', TRUE, 2004, 4, 11, 5, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/06/2004', FALSE, 2004, 4, 11, 6, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/07/2004', FALSE, 2004, 4, 11, 7, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/08/2004', TRUE, 2004, 4, 11, 8, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/09/2004', TRUE, 2004, 4, 11, 9, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/10/2004', TRUE, 2004, 4, 11, 10, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/11/2004', TRUE, 2004, 4, 11, 11, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/12/2004', TRUE, 2004, 4, 11, 12, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/13/2004', FALSE, 2004, 4, 11, 13, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/14/2004', FALSE, 2004, 4, 11, 14, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/15/2004', TRUE, 2004, 4, 11, 15, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/16/2004', TRUE, 2004, 4, 11, 16, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/17/2004', TRUE, 2004, 4, 11, 17, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/18/2004', TRUE, 2004, 4, 11, 18, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/19/2004', TRUE, 2004, 4, 11, 19, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/20/2004', FALSE, 2004, 4, 11, 20, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/21/2004', FALSE, 2004, 4, 11, 21, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/22/2004', TRUE, 2004, 4, 11, 22, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/23/2004', TRUE, 2004, 4, 11, 23, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/24/2004', TRUE, 2004, 4, 11, 24, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/25/2004', TRUE, 2004, 4, 11, 25, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/26/2004', TRUE, 2004, 4, 11, 26, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/27/2004', FALSE, 2004, 4, 11, 27, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/28/2004', FALSE, 2004, 4, 11, 28, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/29/2004', TRUE, 2004, 4, 11, 29, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('11/30/2004', TRUE, 2004, 4, 11, 30, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/01/2004', TRUE, 2004, 4, 12, 1, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/02/2004', TRUE, 2004, 4, 12, 2, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/03/2004', TRUE, 2004, 4, 12, 3, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/04/2004', FALSE, 2004, 4, 12, 4, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/05/2004', FALSE, 2004, 4, 12, 5, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/06/2004', TRUE, 2004, 4, 12, 6, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/07/2004', TRUE, 2004, 4, 12, 7, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/08/2004', TRUE, 2004, 4, 12, 8, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/09/2004', TRUE, 2004, 4, 12, 9, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/10/2004', TRUE, 2004, 4, 12, 10, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/11/2004', FALSE, 2004, 4, 12, 11, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/12/2004', FALSE, 2004, 4, 12, 12, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/13/2004', TRUE, 2004, 4, 12, 13, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/14/2004', TRUE, 2004, 4, 12, 14, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/15/2004', TRUE, 2004, 4, 12, 15, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/16/2004', TRUE, 2004, 4, 12, 16, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/17/2004', TRUE, 2004, 4, 12, 17, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/18/2004', FALSE, 2004, 4, 12, 18, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/19/2004', FALSE, 2004, 4, 12, 19, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/20/2004', TRUE, 2004, 4, 12, 20, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/21/2004', TRUE, 2004, 4, 12, 21, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/22/2004', TRUE, 2004, 4, 12, 22, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/23/2004', TRUE, 2004, 4, 12, 23, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/24/2004', TRUE, 2004, 4, 12, 24, 6)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/25/2004', FALSE, 2004, 4, 12, 25, 7)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/26/2004', FALSE, 2004, 4, 12, 26, 1)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/27/2004', TRUE, 2004, 4, 12, 27, 2)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/28/2004', TRUE, 2004, 4, 12, 28, 3)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/29/2004', TRUE, 2004, 4, 12, 29, 4)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/30/2004', TRUE, 2004, 4, 12, 30, 5)
    Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
    ('12/31/2004', TRUE, 2004, 4, 12, 31, 6)




    Mike Guest

  2. #2

    Default Re: join on 3 tables for asp output

    Mike D wrote: 
    You've done a good job showing us what the data looks like. Now you need to
    show us what the desired results look like. On the face of it, you will need
    some outer joins (I'm assuming this is SQL Server, correct?)

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  3. #3

    Default Re: join on 3 tables for asp output

    Yes, this is SQL 2000. This is what I have been trying:
    SELECT dbo.Tab_Lookup_Calendar.dt,
    dbo.Tab_Instrument_Details.InstrumentName, dbo.Tab_Inst_Schedules.WeekOf
    FROM dbo.Tab_Instrument_Details INNER JOIN
    dbo.Tab_Inst_Schedules ON
    dbo.Tab_Instrument_Details.InstrumentID =
    dbo.Tab_Inst_Schedules.Instrument_ID RIGHT OUTER JOIN
    dbo.Tab_Lookup_Calendar ON
    dbo.Tab_Inst_Schedules.WeekOf = dbo.Tab_Lookup_Calendar.dt
    WHERE (dbo.Tab_Lookup_Calendar.isWeekday = 1) AND
    (dbo.Tab_Lookup_Calendar.DW = 2) AND
    (dbo.Tab_Lookup_Calendar.dt <
    DATEADD(dbo.Tab_Lookup_Calendar.M, 6, GETDATE()))


    I want the data to look like this:

    dt instname Weekof
    10/25/2004 SX01 10/25/2004
    11/1/2004 SX01 11/1/2004
    11/8/2004 SX01 11/8/2004
    11/15/2004 SX01 11/15/2004
    10/25/2004 SX02 Null
    11/1/2004 SX02 11/1/2004
    11/8/2004 SX02 Null
    11/15/2004 SX02 11/15/2004

    I want an entry for every instrument on every Monday. If an instrument is
    not scheduled I want to see a Null or handle it with an IsNull so that I can
    show that the instrument is not scheduled for that week.

    Here is a page with the table I want to populate.
    http://home.comcast.net/~drewerym/3000_Schedule.htm

    Thanks
    Mike








    "Bob Barrows [MVP]" wrote:
     
    > You've done a good job showing us what the data looks like. Now you need to
    > show us what the desired results look like. On the face of it, you will need
    > some outer joins (I'm assuming this is SQL Server, correct?)
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    >[/ref]
    Mike Guest

  4. #4

    Default Re: join on 3 tables for asp output

    What am I missing? Shouldn't this return all rows from Tab_Lookup_Calendar
    that are a Monday? Should be more than 11 rows? Could there be somthing
    wrong with my SQL server?

    SELECT dbo.Tab_Lookup_Calendar.dt, dbo.Tab_Inst_Schedules.Instrument_ID
    FROM dbo.Tab_Lookup_Calendar LEFT OUTER JOIN
    dbo.Tab_Inst_Schedules ON dbo.Tab_Lookup_Calendar.dt =
    dbo.Tab_Inst_Schedules.WeekOf
    WHERE (dbo.Tab_Lookup_Calendar.isWeekday = 1) AND
    (dbo.Tab_Lookup_Calendar.DW = 2) AND
    (dbo.Tab_Lookup_Calendar.dt <
    DATEADD(dbo.Tab_Lookup_Calendar.M, 12, GETDATE())) AND
    (dbo.Tab_Inst_Schedules.Instrument_ID = 1)
    ORDER BY dbo.Tab_Lookup_Calendar.dt








    "Bob Barrows [MVP]" wrote:
     
    > You've done a good job showing us what the data looks like. Now you need to
    > show us what the desired results look like. On the face of it, you will need
    > some outer joins (I'm assuming this is SQL Server, correct?)
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    >[/ref]
    Mike Guest

  5. #5

    Default Re: join on 3 tables for asp output

    Mike D wrote: 
    OK, this query (I've used table aliases to make it more readable), gives you
    a cross join between details and calendar:

    SELECT c.dt, i.InstrumentID, i.InstrumentName
    FROM dbo.Tab_Lookup_Calendar c
    CROSS JOIN dbo.Tab_Instrument_Details i
    WHERE (c.isWeekday = 1) AND
    (c.DW = 2) AND
    (c.dt < DATEADD(m, 6, GETDATE()))
    ORDER BY i.InstrumentName,c.dt

    Now it's simply a matter of joining the results of this query to schedule,
    using an outer join to give you the results you want:

    SELECT dt, InstrumentName, WeekOf
    FROM Tab_Inst_Schedules s RIGHT JOIN
    (SELECT c.dt, i.InstrumentID,i.InstrumentName
    FROM dbo.Tab_Lookup_Calendar c
    CROSS JOIN dbo.Tab_Instrument_Details i
    WHERE (c.isWeekday = 1) AND
    (c.DW = 2) AND
    (c.dt < DATEADD(m, 6, GETDATE()))) q
    ON q.dt=s.WeekOf AND q.InstrumentID = s.Instrument_ID
    ORDER BY q.InstrumentName,q.dt

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  6. #6

    Default Re: join on 3 tables for asp output

    I have been fighting this query for days and I couldn't get it. Thanks so
    much for you help. I'd say I owe you one but if I recall it is more than one!

    Thanks
    Mike

    "Bob Barrows [MVP]" wrote:
     
    > OK, this query (I've used table aliases to make it more readable), gives you
    > a cross join between details and calendar:
    >
    > SELECT c.dt, i.InstrumentID, i.InstrumentName
    > FROM dbo.Tab_Lookup_Calendar c
    > CROSS JOIN dbo.Tab_Instrument_Details i
    > WHERE (c.isWeekday = 1) AND
    > (c.DW = 2) AND
    > (c.dt < DATEADD(m, 6, GETDATE()))
    > ORDER BY i.InstrumentName,c.dt
    >
    > Now it's simply a matter of joining the results of this query to schedule,
    > using an outer join to give you the results you want:
    >
    > SELECT dt, InstrumentName, WeekOf
    > FROM Tab_Inst_Schedules s RIGHT JOIN
    > (SELECT c.dt, i.InstrumentID,i.InstrumentName
    > FROM dbo.Tab_Lookup_Calendar c
    > CROSS JOIN dbo.Tab_Instrument_Details i
    > WHERE (c.isWeekday = 1) AND
    > (c.DW = 2) AND
    > (c.dt < DATEADD(m, 6, GETDATE()))) q
    > ON q.dt=s.WeekOf AND q.InstrumentID = s.Instrument_ID
    > ORDER BY q.InstrumentName,q.dt
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    >[/ref]
    Mike Guest

Similar Threads

  1. Replies: 5
    Last Post: June 27th, 06:35 AM
  2. Listing join from tables...
    By createmedia in forum Coldfusion Database Access
    Replies: 5
    Last Post: June 5th, 09:02 PM
  3. New to Joines - Inner Join on 4 Tables
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: June 14th, 03:14 PM
  4. HELP.. Recordset JOIN tables
    By Addy_31 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 4th, 01:51 AM
  5. SELECT from multiple tables (not join though)
    By Madison Kelly in forum PostgreSQL / PGSQL
    Replies: 15
    Last Post: January 10th, 07:43 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139