How to group DATE column from two or more tables?

sunnyside · #1 (**permalink**) 05-26-07, 21:54

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

r937 · #2 (**permalink**) 05-26-07, 22:10

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?

sunnyside · #3 (**permalink**) 05-27-07, 00:03

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"

r937 · #4 (**permalink**) 05-27-07, 06:09

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'

aschk · #5 (**permalink**) 05-29-07, 04:06

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!

r937 · #6 (**permalink**) 05-29-07, 04:16

aschk, how do you get the 24th from an inner join?

guess again, eh

aschk · #7 (**permalink**) 05-29-07, 04:40

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

aschk · #8 (**permalink**) 05-29-07, 04:53

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...

r937 · #9 (**permalink**) 05-29-07, 04:55

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

sunnyside · #10 (**permalink**) 05-29-07, 23:35

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!

aschk · #11 (**permalink**) 05-30-07, 04:53

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

sunnyside · #12 (**permalink**) 05-30-07, 11:02

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