Hi all,


I am trying to display collective data from 3 MySQL tables:


The query I have, so far, is

SELECT c . * , COUNT( m.id ) AS `members`


FROM `jos_mls_teams` AS `c`


LEFT JOIN `jos_mls_teams_members` AS `m` ON m.teamid = c.id


RIGHT JOIN ( SELECT u.name, u.lastvisitDate FROM `jos_users` AS `u` )


ON u.id = m.userid


GROUP BY c.id


LIMIT 0 , 30



But get the following error:


#1248 - Every derived table must have its own alias


*


A google search results suggested the alias should be put after the bracket. So, I change the code as follow, and move the AS `u` outside the right bracket:

*SELECT c . * , COUNT( m.id ) AS `members`


FROM `jos_mls_teams` AS `c`


LEFT JOIN `jos_mls_teams_members` AS `m` ON m.teamid = c.id


RIGHT JOIN ( SELECT u.name, u.lastvisitDate FROM `jos_users` ) AS `u`


ON u.id = m.userid


GROUP BY c.id


LIMIT 0 , 30*



But then I get the error:


*#1054 - Unknown column 'u.name' in 'field list'*


*


Basically, I need to display all the data from the "jos_mls_teams", total number of members linked to a user from the "jos_mls_teams_members" (basically counting all the rows where the corresponding user's id is in the userid field. Then I want to display that same corresponding user's name & email from another table.




*


Table structures to follow:


*


CREATE TABLE `jos_mls_teams` (
* `id` int(11) NOT NULL auto_increment,
* `userid` int(5) NOT NULL,
* `memberid` int(11) NOT NULL,
* `name` varchar(255) NOT NULL default ',
* `email` text,
* `area` text,


* `arealeader` varchar(150) NOT NULL,
* `founded` text,
* `herder` varchar(100) NOT NULL,
* PRIMARY KEY* (`id`)
) ENGINE=MyISAM* DEFAULT CHARSET=utf8 AUTO_INCREMENT=19 ;


*


*
CREATE TABLE IF NOT EXISTS `jos_users` (
* `id` int(11) NOT NULL auto_increment,
* `name` varchar(255) NOT NULL default ',
* `username` varchar(150) NOT NULL default ',
* `email` varchar(100) NOT NULL default ',


* `password` varchar(100) NOT NULL default ',
* `usertype` varchar(25) NOT NULL default ',
* `block` tinyint(4) NOT NULL default '0',
* `sendEmail` tinyint(4) default '0',
* `gid` tinyint(3) unsigned NOT NULL default '1',


* `registerDate` datetime NOT NULL default '0000-00-00 00:00:00',
* `lastvisitDate` datetime NOT NULL default '0000-00-00 00:00:00',
* `activation` varchar(100) NOT NULL default ',
* `params` text NOT NULL,


* PRIMARY KEY* (`id`),
* KEY `usertype` (`usertype`),
* KEY `idx_name` (`name`),
* KEY `gid_block` (`gid`,`block`),
* KEY `username` (`username`),
* KEY `email` (`email`)
) ENGINE=MyISAM* DEFAULT CHARSET=utf8 AUTO_INCREMENT=73 ;




*


CREATE TABLE IF NOT EXISTS `jos_mls_teams_members` (
* `id` int(11) NOT NULL auto_increment,
* `teamid` int(11) NOT NULL default '0',
* `userid` int(11) NOT NULL default '0',
* `leader` tinyint(1) NOT NULL default '0',


* `sysid` int(11) NOT NULL,
* `memberid` int(11) NOT NULL,
* PRIMARY KEY* (`id`)
) ENGINE=MyISAM* DEFAULT CHARSET=utf8 AUTO_INCREMENT=29**


*


P.S. just to be clear, I want to eventually remove the name & email fields from the `jos_mls_teams` table, since Joomla uses the `jos_users` table for registration purposes and it's easier to use the built-in registration than trying to reinvent the wheel



--
Kind Regards
Rudi Ahlers
SoftDux

Website: http://www.SoftDux.com
Technical Blog: http://Blog.SoftDux.com


Office: 087 805 9573
Cell: 082 554 7532

_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos