Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    43

    Unanswered: Problem with query on badly designed db

    I've asked about this at a couple of other forums, but no one was able to help me so I came to think of this place. I know there are a lot of professionals here and hopefully someone can help me out.

    I've been asked to display a list of calendar events from an SQL Server database, but I'm having trouble. It seems to me that who ever designed this database made some mistakes.

    I'm trying to list all events from tblEventMain, and below every event, a list of all dates and times that the event occurs. The list should be ordered by the earliest date. Every event can have several dates associated with it, but I want to order the list be the earliest of these dates.

    This is what I tried, but I get duplicates no matter what I try since I need to include tblEventTimes in the query to be able to sort the events after the earliest associated date in.
    Code:
    SELECT m.id, m.title, m.arrangedBy, m.location, m.price, m.tickets, m.weblink, c.title, t.[date], t.startTime
    FROM tblEventMain m
    INNER JOIN tblEventTimes t ON m.id = t.eventRef
    INNER JOIN tblEventCategories c ON m.categoryRef = c.id
    WHERE datediff(dd, GETDATE(), t.[date]) >= 0 AND m.categoryRef <> 25
    GROUP BY m.id, m.title, m.arrangedBy, m.location, m.price, m.tickets, m.weblink, c.title, t.[date], t.startTime
    ORDER BY t.[date], t.startTime
    I've attached an image of the tables so you can see how it looks, but please tell me if anything is unclear.

    Thanks very much in advance for any help you can give me!
    Attached Thumbnails Attached Thumbnails db.gif  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, other than naming a datefield "date", this does not look like a poorly designed database. (I wouldn't have stored Time values separate from Date, but whatever...) I think there may be some confusion in what you are trying to do.

    You say you have to include tblEventTimes in the query in order to sort the results, but aren't you actually including it in order to return all the event times associated with an event? I don't see where you are getting duplicate records in you result set, since all your joins are on primary keys. Each row in the result set should be different from any other row, by at least one field.

    I'll take a guess at what you really want.

    Your current sort order is:
    EventDate, EventTime

    What you want is:
    EarliestOccuringEvent, EventDate, EventTime

    Try adding this subquery to your FROM clause:

    INNER JOIN (Select EventRef, Min(DateAdd(d, DateDiff(d, '1/1/9000', [date]), StartTime)) as MinDate
    From tblEventTimes
    Group by EventRef) FirstEvents
    on m.id = FirstEvents.EventRef

    ...and use this as your ORDER BY clause:
    ORDER BY FirstEvents.MinDate, t.[date], t.startTime

    I assumed that your time values stored only time of day, and not the actual date. You may be able to get away with a simpler MIN function.

    blindman

  3. #3
    Join Date
    Mar 2003
    Posts
    43
    Originally posted by blindman
    You say you have to include tblEventTimes in the query in order to sort the results, but aren't you actually including it in order to return all the event times associated with an event?
    Well, I guess I gave up on the idea that I could get all the dates and times aswell, in one query. I guess I thought I would have to use another query for every event, to get its dates and times. What I mean is that if I only select the fields I want from tblEventMain, I don't get duplicates, but then I can't sort them after their associated dates and times in tblEventTimes.
    Originally posted by blindman I don't see where you are getting duplicate records in you result set, since all your joins are on primary keys. Each row in the result set should be different from any other row, by at least one field.
    Right now I seem to get a duplicate of an event for every associated date. You can see the results here:

    http://www2.sodertalje.se/events/list.asp

    Search for example after "O N D S K A N" in the page, and you'll see what I mean.

    Let's say I have two events

    The Movie
    2003-10-10 12:30
    2003-11-10 12:30
    2003-12-10 12:30

    The Exhibition
    2003-10-10 12:00
    2003-12-10 12:30
    2003-13-10 12:30

    In this case, "Exhibition" and its associated dates and times should be displayed first since its earliest date (even though it's stored in two different columns) is earlier than "The Movie has the earliest date, even though it has several. I hope this gives you a clearer idea of how I'm trying to list the events.
    Originally posted by blindman What you want is:
    EarliestOccuringEvent, EventDate, EventTime
    Yes, that's exactly what I'm after. I'll try your suggested subquery and get back with the results.
    Last edited by Oddish; 10-03-03 at 03:38.

  4. #4
    Join Date
    Mar 2003
    Posts
    43
    Since I'm not exactly an SQL wissard, I'm not sure how the query should look after adding your subquery. Here's what it looks like now, and below is a screenshot of the tblEventTimes table so you can see how it looks.
    Code:
    SELECT m.id, m.title, m.arrangedBy, m.location, m.price, m.tickets, m.weblink, c.title AS category, t.[date], t.startTime 
    FROM tblEventMain m 
    INNER JOIN tblEventTimes t ON m.id = t.eventRef 
    INNER JOIN tblEventCategories c ON m.categoryRef = c.id 
    INNER JOIN (SELECT EventRef, MIN(DateAdd(d, DateDiff(d, '1/1/9000', [date]), StartTime)) AS MinDate 
    FROM tblEventTimes 
    GROUP BY EventRef) FirstEvents ON m.id = FirstEvents.EventRef 
    WHERE datediff(dd, GETDATE(), t.[date]) >= 0 AND m.categoryRef <> 25
    ORDER BY FirstEvents.MinDate, t.[date], t.startTime
    And here's the error message I get:
    Adding a value to a 'datetime' column caused overflow.

  5. #5
    Join Date
    Mar 2003
    Posts
    43
    I forgot the screenie:
    Attached Thumbnails Attached Thumbnails tbleventtimes.gif  

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Typo!

    "DateDiff(d, '1/1/9000', [date]), StartTime)"

    should be:

    "DateDiff(d, '1/1/1900', [date]), StartTime)"

    blindman

  7. #7
    Join Date
    Mar 2003
    Posts
    43
    Ok, I had a chance to test it again today, and I'm sorry to say it didn't work. Check out the test page below where I display the query at the top, and then the results below (m.id and m.title).

    http://www2.sodertalje.se/events/test.asp

  8. #8
    Join Date
    Nov 2002
    Location
    England
    Posts
    4
    On behalf of blindman, I will attempt to answer this:

    SELECT m.id,
    m.title,
    m.arrangedBy,
    m.location,
    m.price,
    m.tickets,
    m.weblink,
    c.title,
    t.[date],
    t.startTime
    FROM tblEventMain m
    INNER JOIN (SELECT [eventRef], MIN([date] + [startTime])AS firsttime FROM [eblEventTimes] GROUP BY [eventRef]) sd ON m.[id] = sd.[eventRef] -- Calculates the first time in an event INNER JOIN tblEventTimes t ON m.id = t.eventRef
    AND [date] + [startTime] = sd.firsttime -- Omit this if you want all the times back for an event
    INNER JOIN tblEventCategories c ON m.categoryRef = c.id
    ORDER BY sd.firsttime -- This ensures that the event with the first time comes first
    Last edited by cgHome; 10-08-03 at 13:55.

Posting Permissions

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