join on 3 tables for asp output -- inetserver asp db

Mike D
10/29/2004 8:05:05 AM

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)

Mike D
10/29/2004 8:50:04 AM

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

[quoted text, click to view]

Mike D
10/29/2004 10:55:04 AM

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

[quoted text, click to view]

Bob Barrows [MVP]
10/29/2004 11:14:02 AM

[quoted text, click to view]
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.

Mike D
10/29/2004 11:27:01 AM

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

[quoted text, click to view]

Bob Barrows [MVP]
10/29/2004 2:14:44 PM

[quoted text, click to view]
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.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about inetserver asp db

inetserver asp db : join on 3 tables for asp output