Really REALLY stuck with a join

[Eurisko]

I've got a doozy of a problem... Hope someone can help!

I have a table (table1) that look something like this (I split the dates with a carriage return to make it more readable)

Date | Phone Number | Message
2004-12-01 | 1234 | SUBSCRIBE START
2004-12-01 | 1234 | SUBSCRIBE STOP
2004-12-01 | 4567 | SUBSCRIBE START
2004-12-01 | 7890 | SUBSCRIBE START
2004-12-01 | 6667 | SUBSCRIBE START

2004-12-02 | 9877 | SUBSCRIBE START
2004-12-02 | 6544 | SUBSCRIBE START
2004-12-02 | 4567 | SUBSCRIBE STOP
2004-12-02 | 6544 | SUBSCRIBE STOP
2004-12-02 | 9877 | SUBSCRIBE STOP
2004-12-02 | 3247 | SUBSCRIB START

I need a single query (without using a subquery) that will give the the amount of Numbers that sent SUBSCRIBE STOP the same day they sent SUBSCRIBE START for a particular day.

So for this example it would produce 1 for 2004-12-01 and 2 for 2004-12-02.

Then I need a sinle query (without using a subquery) that will give the amount of Numbers that haven't sent a SUBSCRIBE STOP since they sent SUBSCRIBE START on a particular day.

So for this examle it would produce 2 for 2004-12-01.

I hope I explained that ok If I could use a subquery then it would be simple but I can't. But I've read you can use join's to replace subquerys. Unfortunatley my knowledge of join's isn't that extensive

Thanks

Eurisko

[swampBoogie]

select count(distinct phoneNumber) 
from table1 t1 join table1 t2
on t1.date = t2.date
and t1.phoneNumber = t2.phoneNumber
where t1.message = 'Subscribe start'
and t2.message = 'Subscribe stop'

[Eurisko]

Thanks a million swampBoogie!!! with a few tweaks that did the trick

[Eurisko]

ok that helped me out with the first problem I tried to alter it for the second but have come unstuck again

Can anyone shed some light.

Thanks

Eurisko

[swampBoogie]

select count(distinct phoneNumber) 
from table1 t1 left join table1 t2
on t1.date = t2.date
and t1.phoneNumber = t2.phoneNumber
and t1.message = 'Subscribe start'
and t2.message = 'Subscribe stop'
where t2.phoneNumber is null

[Eurisko]

SwampBoogie... you are da man!