<p>Hi all,</p>
<p>I am trying to display collective data from 3 MySQL tables:</p>
<p>The query I have, so far, is</p>
<div><span class="syntax"><font face="Courier New"><span class="syntax_alpha syntax_alpha_reservedWord">SELECT</span> <span class="syntax_alpha syntax_alpha_identifier">c</span> <span class="syntax_punct">.</span> <span class="syntax_punct">*</span> <span class="syntax_punct syntax_punct_listsep">,</span> <span class="syntax_alpha syntax_alpha_functionName">COUNT</span><span class="syntax_punct syntax_punct_bracket_open_round">(</span> <span class="syntax_alpha syntax_alpha_identifier">m</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">id</span> <span class="syntax_punct syntax_punct_bracket_close_round">)</span> <span class="syntax_alpha syntax_alpha_reservedWord">AS</span> <span class="syntax_quote syntax_quote_backtick">`members`</span> <span class="syntax_white syntax_white_newline"></span><br>
<span class="syntax_alpha syntax_alpha_reservedWord">FROM</span> <span class="syntax_quote syntax_quote_backtick">`jos_mls_teams`</span> <span class="syntax_alpha syntax_alpha_reservedWord">AS</span> <span class="syntax_quote syntax_quote_backtick">`c`</span> <span class="syntax_white syntax_white_newline"></span><span class="syntax_white syntax_white_newline"></span><br>
<span class="syntax_alpha syntax_alpha_reservedWord">LEFT</span> <span class="syntax_alpha syntax_alpha_reservedWord">JOIN</span> <span class="syntax_quote syntax_quote_backtick">`jos_mls_teams_members`</span> <span class="syntax_alpha syntax_alpha_reservedWord">AS</span> <span class="syntax_quote syntax_quote_backtick">`m`</span> <span class="syntax_white syntax_white_newline"></span><span class="syntax_alpha syntax_alpha_reservedWord">ON</span> <span class="syntax_alpha syntax_alpha_identifier">m</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">teamid</span> <span class="syntax_punct">=</span> <span class="syntax_alpha syntax_alpha_identifier">c</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">id</span><span class="syntax_white syntax_white_newline"></span><span class="syntax_white syntax_white_newline"></span><br>
<span class="syntax_alpha syntax_alpha_reservedWord">RIGHT</span> <span class="syntax_alpha syntax_alpha_reservedWord">JOIN</span> <span class="syntax_punct syntax_punct_bracket_open_round">(</span> </font><span class="syntax_alpha syntax_alpha_reservedWord">SELECT</span> <span class="syntax_alpha syntax_alpha_identifier">u</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">name</span><span class="syntax_punct syntax_punct_listsep">,</span> <span class="syntax_alpha syntax_alpha_identifier">u</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">lastvisitDate </span><span class="syntax_alpha syntax_alpha_reservedWord">FROM</span> <span class="syntax_quote syntax_quote_backtick">`jos_users`</span> <span class="syntax_alpha syntax_alpha_reservedWord">AS</span> <span class="syntax_quote syntax_quote_backtick">`u`</span> <span class="syntax_punct syntax_punct_bracket_close_round">)</span><span class="syntax_white syntax_white_newline"></span> <br>
</span><span class="syntax"><span class="syntax_alpha syntax_alpha_reservedWord">ON</span> <span class="syntax_alpha syntax_alpha_identifier">u</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">id</span> <span class="syntax_punct">=</span> <span class="syntax_alpha syntax_alpha_identifier">m</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">userid</span><span class="syntax_white syntax_white_newline"></span><span class="syntax_white syntax_white_newline"></span><span class="syntax_white syntax_white_newline"></span><br>
<span class="syntax_alpha syntax_alpha_reservedWord">GROUP</span> <span class="syntax_alpha syntax_alpha_reservedWord">BY</span> <span class="syntax_alpha syntax_alpha_identifier">c</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">id</span><span class="syntax_white syntax_white_newline"></span><br>
<span class="syntax_alpha syntax_alpha_reservedWord">LIMIT</span> <span class="syntax_digit syntax_digit_integer">0</span> <span class="syntax_punct syntax_punct_listsep">,</span> <span class="syntax_digit syntax_digit_integer">30</span></span></div>
<p>But get the following error:</p>
<p>#1248 - Every derived table must have its own alias</p>
<p> </p>
<p>A google search results suggested the alias should be put after the bracket. So, I change the code as follow, and move the <strong>AS `u`</strong> outside the right bracket: </p>
<div> <span class="syntax"><font face="Courier New"><span class="syntax_alpha syntax_alpha_reservedWord">SELECT</span> <span class="syntax_alpha syntax_alpha_identifier">c</span> <span class="syntax_punct">.</span> <span class="syntax_punct">*</span> <span class="syntax_punct syntax_punct_listsep">,</span> <span class="syntax_alpha syntax_alpha_functionName">COUNT</span><span class="syntax_punct syntax_punct_bracket_open_round">(</span> <span class="syntax_alpha syntax_alpha_identifier">m</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">id</span> <span class="syntax_punct syntax_punct_bracket_close_round">)</span> <span class="syntax_alpha syntax_alpha_reservedWord">AS</span> <span class="syntax_quote syntax_quote_backtick">`members`</span> <span class="syntax_white syntax_white_newline"></span><br>
<span class="syntax_alpha syntax_alpha_reservedWord">FROM</span> <span class="syntax_quote syntax_quote_backtick">`jos_mls_teams`</span> <span class="syntax_alpha syntax_alpha_reservedWord">AS</span> <span class="syntax_quote syntax_quote_backtick">`c`</span> <span class="syntax_white syntax_white_newline"></span><span class="syntax_white syntax_white_newline"></span><br>
<span class="syntax_alpha syntax_alpha_reservedWord">LEFT</span> <span class="syntax_alpha syntax_alpha_reservedWord">JOIN</span> <span class="syntax_quote syntax_quote_backtick">`jos_mls_teams_members`</span> <span class="syntax_alpha syntax_alpha_reservedWord">AS</span> <span class="syntax_quote syntax_quote_backtick">`m`</span> <span class="syntax_white syntax_white_newline"></span><span class="syntax_alpha syntax_alpha_reservedWord">ON</span> <span class="syntax_alpha syntax_alpha_identifier">m</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">teamid</span> <span class="syntax_punct">=</span> <span class="syntax_alpha syntax_alpha_identifier">c</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">id</span><span class="syntax_white syntax_white_newline"></span><span class="syntax_white syntax_white_newline"></span><br>
<span class="syntax_alpha syntax_alpha_reservedWord">RIGHT</span> <span class="syntax_alpha syntax_alpha_reservedWord">JOIN</span> <span class="syntax_punct syntax_punct_bracket_open_round">(</span> </font><span class="syntax_alpha syntax_alpha_reservedWord">SELECT</span> <span class="syntax_alpha syntax_alpha_identifier">u</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">name</span><span class="syntax_punct syntax_punct_listsep">,</span> <span class="syntax_alpha syntax_alpha_identifier">u</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">lastvisitDate </span><span class="syntax_alpha syntax_alpha_reservedWord">FROM</span> <span class="syntax_quote syntax_quote_backtick">`jos_users`</span> <span class="syntax_punct syntax_punct_bracket_close_round">)</span> <span class="syntax_alpha syntax_alpha_reservedWord">AS</span> <span class="syntax_quote syntax_quote_backtick">`u`</span> </span></div>
<div><span class="syntax"></span><span class="syntax"><span class="syntax_alpha syntax_alpha_reservedWord">ON</span> <span class="syntax_alpha syntax_alpha_identifier">u</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">id</span> <span class="syntax_punct">=</span> <span class="syntax_alpha syntax_alpha_identifier">m</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">userid</span><span class="syntax_white syntax_white_newline"></span><span class="syntax_white syntax_white_newline"></span><span class="syntax_white syntax_white_newline"></span><br>
<span class="syntax_alpha syntax_alpha_reservedWord">GROUP</span> <span class="syntax_alpha syntax_alpha_reservedWord">BY</span> <span class="syntax_alpha syntax_alpha_identifier">c</span><span class="syntax_punct syntax_punct_qualifier">.</span><span class="syntax_alpha syntax_alpha_identifier">id</span><span class="syntax_white syntax_white_newline"></span><br>
<span class="syntax_alpha syntax_alpha_reservedWord">LIMIT</span> <span class="syntax_digit syntax_digit_integer">0</span> <span class="syntax_punct syntax_punct_listsep">,</span> <span class="syntax_digit syntax_digit_integer">30</span></span> </div>
<p>But then I get the error:</p>
<p> #1054 - Unknown column '<a href="http://u.name">u.name</a>' in 'field list' </p>
<p> </p>
<p>Basically, I need to display all the data from the "<font face="Courier New"><strong>jos_mls_teams", total number of members linked to a user from the </strong>"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. </font></p>
<p><font face="Courier New"></font> </p>
<p><font face="Courier New">Table structures to follow:</font></p>
<p><font face="Courier New"></font> </p>
<p>CREATE TABLE `jos_mls_teams` (<br> `id` int(11) NOT NULL auto_increment,<br> `userid` int(5) NOT NULL,<br> `memberid` int(11) NOT NULL,<br> `name` varchar(255) NOT NULL default '',<br> `email` text,<br> `area` text,<br>
`arealeader` varchar(150) NOT NULL,<br> `founded` text,<br> `herder` varchar(100) NOT NULL,<br> PRIMARY KEY (`id`)<br>) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=19 ;</p>
<p> </p>
<p> <br>CREATE TABLE IF NOT EXISTS `jos_users` (<br> `id` int(11) NOT NULL auto_increment,<br> `name` varchar(255) NOT NULL default '',<br> `username` varchar(150) NOT NULL default '',<br> `email` varchar(100) NOT NULL default '',<br>
`password` varchar(100) NOT NULL default '',<br> `usertype` varchar(25) NOT NULL default '',<br> `block` tinyint(4) NOT NULL default '0',<br> `sendEmail` tinyint(4) default '0',<br> `gid` tinyint(3) unsigned NOT NULL default '1',<br>
`registerDate` datetime NOT NULL default '0000-00-00 00:00:00',<br> `lastvisitDate` datetime NOT NULL default '0000-00-00 00:00:00',<br> `activation` varchar(100) NOT NULL default '',<br> `params` text NOT NULL,<br>
PRIMARY KEY (`id`),<br> KEY `usertype` (`usertype`),<br> KEY `idx_name` (`name`),<br> KEY `gid_block` (`gid`,`block`),<br> KEY `username` (`username`),<br> KEY `email` (`email`)<br>) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=73 ;</p>
<p> </p>
<p>CREATE TABLE IF NOT EXISTS `jos_mls_teams_members` (<br> `id` int(11) NOT NULL auto_increment,<br> `teamid` int(11) NOT NULL default '0',<br> `userid` int(11) NOT NULL default '0',<br> `leader` tinyint(1) NOT NULL default '0',<br>
`sysid` int(11) NOT NULL,<br> `memberid` int(11) NOT NULL,<br> PRIMARY KEY (`id`)<br>) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=29 </p>
<p> </p>
<p>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 :)</p>
<br clear="all"><br>-- <br>Kind Regards<br>Rudi Ahlers<br>SoftDux<br><br>Website: <a href="http://www.SoftDux.com">http://www.SoftDux.com</a><br>Technical Blog: <a href="http://Blog.SoftDux.com">http://Blog.SoftDux.com</a><br>
Office: 087 805 9573<br>Cell: 082 554 7532<br>