Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2011
    Posts
    5

    Unanswered: More than one field from one column in one query?

    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!

  2. #2
    Join Date
    May 2011
    Posts
    24
    not sure if i understand u right but you can do..

    Code:
    SELECT
    eventname AS monday,
    eventname AS tuesday,
    eventname AS wednesday,
    ..
    FROM calendar_event

  3. #3
    Join Date
    Oct 2011
    Posts
    5
    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!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bStreet View Post
    Hi, I haven't explained this correctly.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2011
    Posts
    5
    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!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  9. #9
    Join Date
    Oct 2011
    Posts
    5
    Quote Originally Posted by r937 View Post
    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
    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.

    Code:
    eventname                            event_start_date
    
    Art Group                              2011-02-07
    Senior Citizens                       2011-02-07
    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'.

    Code:
    eventname                            event_start_date
    
    Footprints                             2011-02-08
    Oasis                                   2011-02-08
    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:

    Code:
    Monday                                Tuesday
    
    Art Group                              Footprints
    Senior Citizens                       Oasis
    Thankyou!

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and of course you don't want any nulls in the output, right?

    the closest you're going to get is like this --
    Code:
    SELECT DATE_FORMAT(event_start_date,'%Y-%m-%d') AS date
         , CASE WHEN event_start_date BETWEEN '2011-02-07' AND '2011-02-08'
                THEN event_name ELSE NULL END  AS Monday   
         , CASE WHEN event_start_date BETWEEN '2011-02-08' AND '2011-02-09'
                THEN event_name ELSE NULL END  AS Tuesday  
         , CASE WHEN event_start_date BETWEEN '2011-02-09' AND '2011-02-10'
                THEN event_name ELSE NULL END  AS Wednesday
         , CASE WHEN event_start_date BETWEEN '2011-02-10' AND '2011-02-11'
                THEN event_name ELSE NULL END  AS Thursday 
         , CASE WHEN event_start_date BETWEEN '2011-02-11' AND '2011-02-12'
                THEN event_name ELSE NULL END  AS Friday   
         , CASE WHEN event_start_date BETWEEN '2011-02-12' AND '2011-02-13'
                THEN event_name ELSE NULL END  AS Saturday 
         , CASE WHEN event_start_date BETWEEN '2011-02-13' AND '2011-02-14'
                THEN event_name ELSE NULL END  AS Sunday   
      FROM arc_calendar_event
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Oct 2011
    Posts
    5
    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!
    Last edited by bStreet; 10-27-11 at 10:48.

Posting Permissions

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