Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2003
    Posts
    30

    Question Unanswered: How to group DATE column from two or more tables?

    There are 2 tables (let's say 2 tables first), in each table there is a date field (YYYY-mm-dd). I want to Join these 2 tables together if they have same "classId", also I want the result group by date.

    Example: I want to find the different date when classId = "windows"

    table_1
    -------------------------
    2007-05-22 | windows |
    2007-05-26 | office |
    -------------------------

    table_2
    -------------------------
    2007-05-22 | windows |
    2007-05-24 | windows |
    -------------------------

    I want the result is: (group by the date)

    2007-05-22 | windows
    2007-05-24 | windows

    is there any way can perform this? thanks
    Last edited by sunnyside; 05-27-07 at 01:03.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm quite certain there is a way to do it, but there are a number of things about your question that don't make sense

    why do you want GROUP BY? what happened to the row for the 26th? why it is excluded by the 24th is included?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2003
    Posts
    30
    Quote Originally Posted by r937
    i'm quite certain there is a way to do it, but there are a number of things about your question that don't make sense

    why do you want GROUP BY? what happened to the row for the 26th? why it is excluded by the 24th is included?
    Oh, I didn't make my self clear. My result is based on if I pick classId = "windows"

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i still don't understand what you want so i will guess

    Code:
    select date,class
      from table_1
     where class='windows'
    union    
    select date,class
      from table_2
     where class='windows'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Ooh, can i guess too?

    Code:
    SELECT classId
              ,date
    FROM table_1 t1 
    JOIN table_2 t2 ON t1.classId=t2.classId
    ORDER BY date ASC
    Please define what you mean by GROUP BY because GROUP BY is an SQL clause, and should ONLY be used with AGGREGATE functions, none of which you are implementing in your suggestion.

    Clarify!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aschk, how do you get the 24th from an inner join?

    guess again, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Ooh I get another guess :

    Code:
    SELECT * 
    FROM planet_moon pm
    LEFT JOIN star_system ss ON pm.interstellar_activity = ss.asteroid_range
    WHERE asteroid_range > 3426 
    AND intersteller_activity > 3
    AND planets > 1
    GROUP BY solar_system
    ORDER BY cloud_dust_ratio

    p.s. sorry for the cynical reply. Sunnyside needs to clarify

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    It looks like he is looking to Join the two tables (on classId) as he described, then GROUP BY date. The problem is that is using the GROUP BY clause completely wrongly.

    e.g.

    SELECT t2.classId,t2.date
    FROM table_1 t1
    JOIN table_2 t2 ON t1.classId=t2.classId
    GROUP BY date

    For the datasets provided above :

    table_1
    -------------------------
    2007-05-22 | windows |
    2007-05-26 | office |
    -------------------------

    table_2
    -------------------------
    2007-05-22 | windows |
    2007-05-24 | windows |
    -------------------------

    You will get a result of
    JOIN ON "windows"
    --------------------------------------------------
    2007-05-22 | windows | 2007-05-22 | windows |
    2007-05-24 | windows | 2007-05-22 | windows |
    --------------------------------------------------

    Select ONLY t2 result parts (date,classId)
    ----------------------
    2007-05-22 | windows
    2007-05-24 | windows
    ----------------------

    GROUP BY date
    ----------------------
    2007-05-22 | windows
    2007-05-24 | windows
    ----------------------

    Of course this is complete crap... because it uses the GROUP BY clause really badly.

    What i'm most curious about is how these two tables exist concurrently because it seems they contain the same information...

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aschk
    Code:
    FROM planet_moon pm
    LEFT JOIN star_system ss ON pm.interstellar_activity = ss.asteroid_range
    WHERE asteroid_range > 3426 
    AND intersteller_activity > 3
    two comments -- you've defeated the purpose of the LEFT OUTER JOIN (this query will produce only inner join results), and the condition on interstellar_activity being greater than 3 is totally superfluous, redundant, unnecessary, and redundant

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2003
    Posts
    30
    Sorry, guys. I was too busy to come here these a few days. Actually, right now I used another way to figure this problem out. (can't just UNION or JOIN these tables, too complicated)

    Thank you all!

  11. #11
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What problem? You still haven't explained what it was

    "can't just UNION or JOIN these tables, too complicated"

    That entirely depends on what you're looking for. And I would say that you CAN use both of those on your tables... I would also hope that you don't use UNION/JOIN interchangeably

  12. #12
    Join Date
    Aug 2003
    Posts
    30
    Quote Originally Posted by aschk
    What problem? You still haven't explained what it was

    "can't just UNION or JOIN these tables, too complicated"

    That entirely depends on what you're looking for. And I would say that you CAN use both of those on your tables... I would also hope that you don't use UNION/JOIN interchangeably
    No, of course I didn't use "union/join" together.

    The entire problem is: I have 3 tables, each table contains a date field (2 of 3 are in format "YYYY-mm-dd", another one uses format as "YYYY/mm/ddThh:mm:ss"). Also all these tables have a Key field (classID). My query is : fetch all rows in these 3 tables which have the same "classID", also will group by date: for example: 2007-05-22 has two rows (from 2 tables), 2007-05-23 has 5 rows (from all these 3 tables).....

    Hope you understand what I'm saying

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sunnyside
    Hope you understand what I'm saying
    nope, it's still fuzzy, and it still sounds like UNION

    do you perhaps mean ORDER BY when all along you've been saying "group by"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Aug 2003
    Posts
    30
    Quote Originally Posted by r937
    nope, it's still fuzzy, and it still sounds like UNION

    do you perhaps mean ORDER BY when all along you've been saying "group by"
    No, it has to be "group by". One same date can contains multiple rows of info.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sunnyside
    No, it has to be "group by".
    fine

    would you be willing to walk us through your original sample data, then?

    because it still doesn't make sense

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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