CentOS - May 2010 - I need some help joining data from 3 MySQL tables, please

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://lists.centos.org/pipermail/centos/attachments/20100501/3364c551/attachment-0001.html>

Rudi Ahlers wrote:
> 
> 
> 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 <http://u.name>' in
'field list'
Inside the subquery, the table is jos_users (and since there is only one you 
shouldn't even have to specify it.  The 'u' alias is a temporary
table holding
the result from the subquery.

-- 
   Les Mikesell
    lesmikesell at gmail.com