UNION with Group By

[JMay0816]

I am selecting records from two tables:

select a.fileid, a.outby, a.adddate, b.email from table1 a, table2 b where a.outby=b.uid and a.adddate < date_add(now(), interval -6 day) and a.status in (1,2) UNION
select a.fileid, a.outby, a.adddate, b.email from table3 a, table2 b where a.outby=b.uid and a.adddate < date_add(now(), interval -6 day) and a.status in (1,2)

This returns 4 records:
+---------+--------+------------+-------------------+
| fileid | outby | adddate | email |
+---------+--------+------------+-------------------+
| 0001013 | user01 | 2007-03-31 | User.01@email.com |
| 0001014 | user02 | 2004-02-02 | User.02@email.com |
| 0000096 | user01 | 2006-12-04 | User.01@email.com |
| 0000097 | user01 | 2007-03-30 | User.01@email.com |
+---------+--------+------------+-------------------+

I am trying to group this result set by email. Thus my output should only be:

User.01@email.com
User.02@email.com

Can't seem to hit on the right combination. Can someone assist?

thx

 

[feherke]

Hi

select a.fileid, a.outby, a.adddate, b.email from table1 a, table2 b where a.outby=b.uid and a.adddate <  date_add(now(), interval -6 day) and a.status in (1,2)  UNION
select a.fileid, a.outby, a.adddate, b.email from table3 a, table2 b where a.outby=b.uid and a.adddate <  date_add(now(), interval -6 day) and a.status in (1,2) [red]order by email[/red]

Feherke.

http://rootshell.be/~feherke/

 

[JMay0816]

that still returns four records instead of the desired two.

 

[JMay0816]

Finally got it:

select distinct email from (
select a.fileid, a.outby, a.adddate, b.email from table1 a, table2 b where a.outby=b.uid and a.adddate < date_add(now(), interval -6 day) and a.status in (1,2) UNION
select a.fileid, a.outby, a.adddate, b.email from table3 a, table2 b where a.outby=b.uid and a.adddate < date_add(now(), interval -6 day) and a.status in (1,2) order by email
) as newmail

 

[JMay0816]

Finally got it:

select distinct email from (
(select a.fileid, a.outby, a.adddate, b.email from table1 a, table2 b where a.outby=b.uid and a.adddate < date_add(now(), interval -6 day) and a.status in (1,2)) UNION
(select a.fileid, a.outby, a.adddate, b.email from table3 a, table2 b where a.outby=b.uid and a.adddate < date_add(now(), interval -6 day) and a.status in (1,2))
) as newmail

 

[feherke]

Hi

Oops. I thought that sample was only for order not for count.

I am glad to see you solved it.

Feherke.

http://rootshell.be/~feherke/