how do i get the member count using joins

here is my database structure

              members table
                      member_Id


              membertomshiptable
                      membertomship_startdate
                      membertomship_enddate 
                      member_id
                      mshipstatustype_id

              membershipstatustypetable

                   membershipstatustype_id
                   membershipstatusname

I have membershipstatusname cloumn has value is completed . can i get how many members(count) have membershipstatusname is completed starting date from membertomship_startdate.

and i have a doubt , I need to represent this on graph i hav to take days on x-axis and i hav to take members ids on y axis and i want to represent the how many members are having completed membershipstatus name with in one month......

is it possible with query

can any one help on this one

Answers


SELECT membertomship_startdate,
           COUNT(member_id)
FROM       members  t1,

               membertomshiptable t2,
               membershipstatustypetable t3,
WHERE  t1.member_id=t2.member_id AND

            t2.mshipstatustype_id= t3.membershipstatustypetable  AND
            t2.membertomship_startdate> <specify the date here>  AND
            t3.membershipstatusname='completed' AND
GROUP BY   t2.membertomship_startdate

Could this be what you're looking for?

SELECT     `membertomship_startdate`,
           `membershipstatusname`,
           COUNT(`member_id`)
FROM       `membertomshiptable`
LEFT JOIN  `membershipstatustypetable` ON
           `membershipstatustypetable`.`membershipstatustype_id` = `membertomshiptable`.`mshipstatustype_id`
GROUP BY   `membertomship_startdate`,
           `membershipstatusname`;

Or to search for "completed" membership where the "start date" is greater than certain date:

Could this be what you're looking for?

SELECT     `membertomship_startdate`
           COUNT(`member_id`)
FROM       `membertomshiptable`
INNER JOIN `membershipstatustypetable` ON
           `membershipstatustypetable`.`membershipstatustype_id` = `membertomshiptable`.`mshipstatustype_id` AND
           `membershipstatusname` = "completed"
WHERE      `membertomship_startdate` > "0000-00-00" AND
GROUP BY   `membertomship_startdate`;

SELECT COUNT(member_id) JOIN membertomshiptable USING(member_id)
     JOIN membershipstatustypetable ON mshipstatustype_id=membershipstatustype_id
     WHERE membertomship_startdate <= 'XXXX-XX-XX' 
     AND membershipstatusname='complete'

If you know the status ID you want to check you could avoid the last JOIN. You might also need to specify an end date as well if you're looking for a specific month.


Need Your Help

How to calculate numbers of Threads?

java multithreading

How to calculate numbers of threads , if i create two new Threads in method run ? Its creates many times , and fall when memory is over .

error when anonymous type is used in join query in LINQ

linq anonymous-types query-expressions

I start learning LINQ these days. I got an error when I try to return an anonymous type in join query. And I don't understand why I get this error.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.