SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    selecting from one column, but getting two back

    Alright. I have a table where all entries have a datetime field for when it was entered. I want to select from columnA with one date range, and then again with a second date range from the same column. In my result I want two separate columns returned. For selecting a date range I'm doing "columnA >= '2009-03-02 08:45:01' and columnA <= '2009-03-02 08:56:41'". Is it possible to add another range to get a second column returned?

    This is MySQL BTW.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I really wouldn't recommend doing this with Sql. I suggest gathering the necessary data from the database, and then manipulating it with server-side code.

    Doing this with SQL can get kinda tricky. You'll need to do something kinda like this:

    SELECT id, table1.columnA as [range1], table2.columnA as [range2] FROM myTable
    LEFT OUTER JOIN (SELECT id, columnA FROM myTable WHERE columnA BETWEEN '3/1/2009' AND '3/31/2009') AS table1 ON id = table1.id
    LEFT OUTER JOIN (SELECT id, columnA FROM myTable WHERE columnA BETWEEN '3/1/2008' AND '3/31/2008') AS table2 ON id = table2.id
    iiicreative.com - Louisiana Web Design
    Innovation. Intuition. Imagination + Creative

    Follow me on twitter!

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nemesis_256 View Post
    I want to select from columnA with one date range, and then again with a second date range from the same column. In my result I want two separate columns returned.
    i tried a couple of times to understand what you want, and failed

    perhaps you could show a few sample rows of data, and then show what the query is supposed to return?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict Poiesis01's Avatar
    Join Date
    Jun 2007
    Location
    Cape Town
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get what you want. You want to query the same column using to filters but you want the results to be returned as two columns. It sounds like you need a subquery something like bholli suggests but I just can't get my head around it Think I need some sleep and see if I can get you a solution in the morning...

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    two columns without a key to tie their rows together?

    this type of juxtapositioning of unrelated data is best handled in the application layer, not SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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