SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Thread: Really REALLY stuck with a join
-
Jan 5, 2005, 11:22 #1
- Join Date
- Oct 2004
- Location
- Manchester
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Really REALLY stuck with a join
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 okIf 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
-
Jan 5, 2005, 12:40 #2
- Join Date
- Jan 2004
- Location
- Uppsala, sverige
- Posts
- 700
- Mentioned
- 2 Post(s)
- Tagged
- 1 Thread(s)
Code: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'
-
Jan 5, 2005, 13:07 #3
- Join Date
- Oct 2004
- Location
- Manchester
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks a million swampBoogie!!! with a few tweaks that did the trick
-
Jan 5, 2005, 14:08 #4
- Join Date
- Oct 2004
- Location
- Manchester
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Jan 7, 2005, 03:32 #5
- Join Date
- Jan 2004
- Location
- Uppsala, sverige
- Posts
- 700
- Mentioned
- 2 Post(s)
- Tagged
- 1 Thread(s)
Code: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
-
Jan 7, 2005, 17:24 #6
- Join Date
- Oct 2004
- Location
- Manchester
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
SwampBoogie... you are da man!
Bookmarks