[thelist] Help with SQL query JOIN using ASP/VBScript

[Tab Alleman]

The output you're after is often called a "Crosstab", and can't be done with simple SQL joins.  With SQL Server you could write a stored procedure to handle this, but since you're using Access, I'd recommend you handle this in the VBScript code.  You can either:

1.  Get one big recordset, and as you iterate through it, check to see if the event name has changed.  If it hasn't, just print the date without printing the name of the event again.

2.  Get a recordset of event titles, and as you iterate through it, do another query on each of them to get the dates for that event.

HTH.

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org
> [mailto:thelist-bounces at lists.evolt.org]On Behalf Of Pringle, Ron
> Sent: Thursday, April 13, 2006 11:33 AM
> To: theList (E-mail)
> Subject: [thelist] Help with SQL query JOIN using ASP/VBScript
> 
> 
> Hi all-
> 
> I'm using classic ASP/VBScript and querying an Access DB. I'm 
> currently running some queries on different "events" pages 
> where I grab events from an events table and a dates table 
> (plus other tables as well), joining them on a PK field of eventID.
> 
> Normally this query populates a calendar of events, so it 
> creates a full record of each event for each date. Now I've 
> been asked to create a list of events for various departments 
> and show it as a list, ordered by date, on the page. No 
> problem, done. However, it looks pretty silly to show the 
> same event title and description over and over again when it 
> has multiple dates, and I think it would be confusing to the end user.
> 
> So instead I want to list those events once, with multiple 
> dates attached to them. Each date would then be a link to the 
> full record containing the specific date/time info for that 
> event date.
> 
> My current query is below. How do I need to modify this to 
> accomplish what I want? I still have problems wrapping my 
> head around JOINs.
> 
> Current Query:
> 
> SELECT eventType, eventDate, dateID, title, summary, 
> eventTimeStart FROM ((tbl_event EV LEFT OUTER JOIN 
> tbl_eventDates ED ON EV.eventID = ED.eventID) LEFT OUTER JOIN 
> tbl_contact C ON EV.eventID = C.eventID) LEFT OUTER JOIN 
> tbl_link L ON EV.eventID = L.eventID WHERE EV.deptName = 
> 'Police Department' AND EV.title LIKE '%%"& dynSearch & "%%' 
> AND ED.eventDate >= # " + Replace(currentDate, "'", "''") + " 
> # ORDER BY ED.eventDate ASC, ED.eventTimeStart ASC
> 
> 
> TIA,
> Ron
> 
> 
> -- 
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester 
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt ! 
>