You need to look into cross-tablualtion / Pivot Tables. There is a good article on mysql.com
http://dev.mysql.com/tech-resources/articles/wizard/print_version.html On Aug 9, 2011, at 8:04 AM, Anna P wrote: > Hello. > > It's maybe not such a CakePHP question, but rather regarding a rather > complex (for me) SQL query. > > I have a table storing e-mail addresses to which has been sent an > invite to a website: > > CREATE TABLE IF NOT EXISTS `recomm_emails` ( > `id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT, > `user_id` smallint(5) unsigned NOT NULL, > `email` varchar(100) NOT NULL, > `registered` tinyint(1) NOT NULL DEFAULT '0', > `active` tinyint(1) NOT NULL DEFAULT '0', > `created` date NOT NULL, > PRIMARY KEY (`id`) > ) > > 'user_id' is the ID of registered user, who has sent an invitation to > given e-mail address. > The 'registered' field means that user who got the invitation has > registered in website. The 'acitve' field holds the information if > invited user has activated his account after registration. > > The problem is, I have to create a statistics of invitations. List > should be divided into pages and should contain: > - User.id and User.login_name (both from users table), > - total number of all invited users, > - number of users who registered and activated account (registered=1 > and active=1), > - number of users who registered but didn't activate the account > (registered=1 and active=0) > - number of users who didn't register at all (registered=0). > I need this kind of list, because it will have to be also ordered by > selected parameters (chosen from 4 numbers above). So everything must > be in one query. > > How to write such a query? I can't come up with solution for a long > time! Please help!:) > > I first came up to an idea to do it by PHP, but it is not a very nice > solution, because on one page I shall present about 30 records, and > there is no point to get data from whole the users table (which can > have let's say couple tousend of records). > > > -- > Our newest site for the community: CakePHP Video Tutorials > http://tv.cakephp.org > Check out the new CakePHP Questions site http://ask.cakephp.org and help > others with their CakePHP related questions. > > > To unsubscribe from this group, send email to > [email protected] For more options, visit this group at > http://groups.google.com/group/cake-php -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php
