SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    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 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

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    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'

  3. #3
    SitePoint Member
    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

  4. #4
    SitePoint Member
    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

  5. #5
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    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

  6. #6
    SitePoint Member
    Join Date
    Oct 2004
    Location
    Manchester
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SwampBoogie... you are da man!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •