Hello, would anyone be able to tell me if it is possible to have more than one field from the same source in the same query.

"SELECT eventname AS Monday,
FROM calendar_event
WHERE event_start_date BETWEEN '2011-02-07' AND '2011-02-08'"

I would like to be able to do another one for Tuesday, i.e. "SELECT eventname AS Tuesday" etc, and then all days of the week, so that the info is all coming from the one table but separate fields.

Thank you!

not sure if i understand u right but you can do..

Hi, I haven't explained this correctly. I need Monday to show the events from the date specified in the WHERE clause, but Tuesday will need its own WHERE clause, as will all the other days.

Otherwise this will return all the same events because they will all use the same WHERE clause. So, is there a way for each field to have its own WHERE clause?

Thank you!

sorry I don't understand
do you mean you want to put a date bracket AND a specific day element on the where clause?

Id have a look at the MySQL date / time functions and see if those could help

that's for sure

can you show a couple of rows of data from the calendar_event table, and then show the results that you want the query to produce from those rows

Sorry, I'm not great at trying to get my thoughts onto post!

Ok, I'm using iReport to create the calendar, so I'm trying to figure out to do this within MySQL.

The data in the 'calendar_event' table contains all the information I need about the different events, and within that table is a column called 'eventname'. There is also a column within the same table called 'event_start_date', which contains the date of the event.

So, different events take place on different days. What I would like is to have 7 different fields for each day of the week so that they contain only the events that are taking place on that day.

This is where the 'WHERE' comes in. I use 'WHERE' to limit the field to choosing only an event from a specific day. All the data comes FROM the one table, 'calendar_event', and one column within that table, 'eventname', and the WHERE clause gets its info from 'event_start_date'.

Something like this, with correct syntax though -

SELECT DATE_FORMAT(event_start_date,'%Y-%m-%d') AS date,

eventname AS Monday WHERE event_start_date BETWEEN '2011-02-07' AND '2011-02-08',
eventname AS Tuesday WHERE event_start_date BETWEEN '2011-02-08' AND '2011-02-09',
eventname AS Wednesday WHERE event_start_date BETWEEN '2011-02-09' AND '2011-02-10,
eventname AS Thursday WHERE event_start_date BETWEEN '2011-02-010' AND '2011-02-11',
eventname AS Friday WHERE event_start_date BETWEEN '2011-02-11' AND '2011-02-12',
eventname AS Saturday WHERE event_start_date BETWEEN '2011-02-12' AND '2011-02-13',
eventname AS Sunday WHERE event_start_date BETWEEN '2011-02-13' AND '2011-02-14'

FROM arc_calendar_event

That is basically what I'm trying to achieve! Thank you!

you're still not out of the woods yet

it would be easy to do ~if~ you were completely sure that there would ever be only one event per date

which is why i asked you to please show a couple of rows of data from the calendar_event table, and then show the results that you want the query to produce from those rows

especially if more than one event can take place on the same date

please show how you want the results to look

Also have a look at '%a' with DATE_FORMAT as this will return the day of week that date represents. This might make you solution more generic rather than comparing between two dates.

There will be more than one event on most days.
There are quite a few columns, but I only need data from 2 of them.

This is bascially how it looks, without all the other data which I dont need for this. These events both take place on a Monday, so I want these to appear in a field called 'Monday'.

These events take place on a Tuesday, so I would want them to appear in a field called 'Tuesday'.

So, I would want one field for each:

Thankyou!

and of course you don't want any nulls in the output, right?

the closest you're going to get is like this -- but as you can see, this prints out only one event per row, however, it ~is~ in the right column

you really need to be doing this type of cosmetic re-arrangement of query results in the application layer, not with SQL

Thankyou! This didn't work, but thank you anyway.
I'm trying to work it out in iReport, but not getting very far.
Thank you!