| home | about me | feed RSS feed

I've posted more of these on stackoverflow.com, but this is all I've found for the moment (more to come!):

SELECT COUNT(p1.payment_id) AS signup_count,
       SUM(p1.amount)       AS signup_amount  

  FROM payments p1

       LEFT JOIN payments p2 ON p1.member_id = p2.member_id
   AND p2.completed = 1
   AND p2.tm_completed < '2009-05-01'

 WHERE p1.completed > 0
   AND p1.tm_completed BETWEEN '2009-05-01' AND '2009-05-30'
   AND p2.member_id IS NULL

Another one with a little bit of a twist:

  SELECT t1.grade_id, t1.grade_name, t1.price, t1.update_date
    FROM my_tbl t1
         LEFT JOIN my_tbl t2 ON t2.grade_id = t1.grade_id
     AND t2.update_date > t1.update_date
     AND t2.update_date < CURRENT_DATE
   WHERE t1.update_date < CURRENT_DATE
     AND t2.grade_id IS NULL
ORDER BY t1.grade_name;

A more simple example in case all of this is becoming a bit much (it is for me!):

SELECT [some cols]
  FROM item i
       LEFT JOIN item children ON children.parent_id = i.id
 WHERE children.id IS NULL;
created: 2010-06-23 | updated: 2011-06-18


comments:
on 2010-06-30 Miruska wrote:

Muj mily Adamku, uz Ti zase pisu koment irelevant to your blog post :-) I like the little window corner, hope it shows in other browsers than G-Chrome. Pusinky

on 2010-07-11 adam wrote:

Firefox doesn't seem to have the nifty textarea resize-tab-thing :-( Maybe Mozilla will add for Firefox 4.

Comments are now closed for this post.