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.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.startTimeI'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!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|||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, EventTimeYes, that's exactly what I'm after. I'll try your suggested subquery and get back with the results.|||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.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.|||I forgot the screenie:|||Typo!

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

should be:

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

blindman|||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|||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