I have a question.
Imagine a subscribers table, that has 3 columns (PersonID, FromDate, ToDate).
How you can find out that which person isn't subscriber in this month?


PersonID   FromDate    ToDate
------------------------------------------
  5                   1              2
  5                   2              3
10                  1              2  

In this example, PersonID 10, isn't subscriber in month 2.



____________________________
Persia

Well, in MySQL it'd be like:

SELECT DISTINCT s.`PersonID` FROM `subscriptions` s INNER JOIN `subscriptions` s2 ON s.PersonID=s2.PersonID WHERE s2.ToDate>2

____________________________
Everywhere's Local (classifieds, job postings, & more for everycity in the world - user entered)

No it's not correct.
if the query be changed to

SELECT DISTINCT s.PersonID  FROM tbl s
INNER JOIN tbl s2
ON s.PersonID=s2.PersonID
WHERE not(s2.FromDate>2)

It also returns PersonID 5, which is subscriber from month 1 to 2.


____________________________
Persia

This will do it :-
SELECT     S2.PersonID
FROM         subscriptions S1 RIGHT OUTER JOIN
                      subscriptions S2 ON S1.PersonID <> S2.PersonID
WHERE     (S1.FromDate = 2) AND (S1.ToDate = 3)



____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Given how your database is setup, it'd actually be easier (and a lot clearer to understand, with less overhead) if you used dates instead of 1, 2, 3, etc.  Or even just have a "subscriber for month" field only (as 'from 1 to 2' would be the same as 'subscriber in month 1')

If you just used one field with a subscriber or date fields, you'd only need one row per user, and it'd make queries much easier for everything (not subscriber this month ex: "select * fom tbl where subscription_month < cur_month")


[Edited by JLRodgers on 06-01-2009 at 03:22 PM GMT]

____________________________
Everywhere's Local (classifieds, job postings, & more for everycity in the world - user entered)