Query troubles

bertvdk

Hello everybody,

I've got query-troubles... who can help me?

2 tables:

Table dates:

ID_event int(11)

date date

Table events:

ID int(11) auto_increment

place varchar(128)

title varchar(100)

... (other event data)

On the homepage I would like to list the events for the next 2 weeks.
At the moment I run following query:

SELECT dates.date, events.* 
FROM dates, events
WHERE dates.ID_event = events.ID AND dates.date >= '2004-05-01' AND dates.date <= '2004-05-14'
ORDER BY dates.date

This query gives me the following problem.
An event that runs from 2004-05-04 till 2004-05-10 will be shown 6 times, I only want to show this 1 time.

Any ideas how I should alter my query?

Thanks,
Bert

PS: I tried join queries but they give me serious headaches 🙁

bertvdk

OK...

I've altered my query, I've added GROUP BY...

SELECT dates.date, events.*
FROM dates, events
WHERE dates.ID_event = events.ID AND dates.date >= '2004-05-01' AND dates.date <= '2004-05-14'
GROUP BY events.ID ORDER BY dates.date

It seems to work 😉
But is this the right way to go?

Thanks,
B.

bertvdk

OK this is causing a new problem 🙁

I'd like to sort the events by their first date.
Using the above query sorts them by their last...

Sorting them by their ID is not an option as the events are not entered in the database chronologicaly.

So I think I need a completely different query 🙁

Any ideas?
Thanks,
B.