Hi all,
I am trying to display collective data from 3 MySQL tables:
The query I have, so far, is:
Code:
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:
Quote:
#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:
Code:
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:
Quote:
#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:
Code:
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
Linear Mode
