[RESOLVED] union, max and min

[nuttynibbles]

hi, i need to select data from 2 different tables. im using union.

the fields are activity_id, user_id, start_date and end_date. there are many activities and each user can attend these activities multiple times. for each user, i need to find the min start date and max end date for all the activities they attended.

so the final result will show each user, and the min start date and max end date

This is my sql:

SELECT qa.userid AS userid,q.id as act_id, MIN(from_unixtime(qa.start_date)) AS start_time,MAX(from_unixtime(qa.end_date)) AS end_time, 'swim' FROM swmimming q, swmimming_attemp qa, user u
WHERE q.id=qa.id AND qa.userid=u.id AND qa.id IN(1,2,3)
UNION
SELECT la.userid AS userid,la.lessonid as act_id,MIN(from_unixtime(la.start_date)) AS start_time, MAX('end_date') AS end_time, 'run' FROM running l, running_attemp la, user u
WHERE l.id=la.id AND la.userid=u.id AND la.id IN(1,2) GROUP by userid

[Sxooter]

First union, then put that in a subselect to get min / max..

select userid, min(field1), max(field2) from (
    select userid, field1, field2 from tablea
    union all
    select userid, field1, field2, from tableb
) as un
group by userid;

[nuttynibbles]

hi, thk so much.. the result is almost what i wanted. however, i would need smth more..

for e.g: a user attended swmming and start_date= 2009-03-09 15:07:28, end_date=2009-03-09 15:07:16

he also attended running and start_date=2009-03-09 15:08:02 and end_date=2009-03-09 15:08:10

so the result should based on these 2 activities and select the result as
min start date = 2009-03-09 15:07:28 and max end_date=2009-03-09 15:08:10

is it possible to do so??

[Sxooter]

Since we're only grouping by userid I'd expect the output to be what you are asking for. Have you tested my query with your dataset yet to see if it works?

[nuttynibbles]

okie i got it to work..it was my bad. =)
many tks to you..

[nuttynibbles]

okay i got it to work ealier but soon after i populate my db with more data i realise the results is not accurate.. for max end_date, it will pick the one with same date as min start date. so for e.g min start_date is 2009-03-09 15:03:05, and max end date is 2009-03-12 10:56:33, it will pick the max time for 9 march instead @ 2009-03-09 17:03:05

the dates are store as timestamp..
what i did was from_unixtime(qa.timestart) AS end_time and after which i max end_time

[nuttynibbles]

hmm okie nvm i solved it =)

[Sxooter]

Cool, what did you come up with? (it might be instructional for other users...)