I need some help joining data from 3 MySQL tables, please

[SilverNodashi]

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

 

[dinkd]

replying from my phone... but try this..

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` ) as `d`

ON d.id = m.userid

GROUP BY c.id

LIMIT 0 , 30

 

[ToxicBunny]

Ok, I think I'm having an issue understanding how you want the data formatted...... and I'm also not a MySQL person.. i work in MS SQL all day long....

don't think the structure of your query is right though, you can't select columns in the middle of a join unless MySQL is VERY different to MS SQL.

I personally would have started with something like the following then I'd wittle down the data to what I want...

SELECT * from jos_mls_teams mlst LEFT JOIN jos_mls_team_members mlstm ON mlst.id = mlstm.teamid
RIGHT JOIN jos_users us on mlst.id= us.id



Althought looking again... I'd probably have to restructure everything to get the data you want out of MS SQL given the way it handles joins.... :|

 

[SilverNodashi]

replying from my phone... but try this..

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` ) as `d`

ON d.id = m.userid

GROUP BY c.id

LIMIT 0 , 30

Thanx, I did try that way around as well. Here's my version:

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` ) AS `users` 
ON u.id = m.userid
GROUP BY c.id
LIMIT 0 , 30

So, I used the word "users" as an alias, where you have a "d" as an alis"


But got this error:



#1054 - Unknown column 'u.id' in 'on clause'

 

[dinkd]

perhaps this will work better... don't have MYSQL so its written in notepad lol

select c.*, ut.UserCount

FROM 'jos_mls_teams' as 'c'

LEFT JOIN

(SELECT m.teamid, count(u.id) as 'UserCount'

FROM `jos_mls_teams_members` AS `m`

RIGHT JOIN 'jos_users' as 'u'

ON m.userid = u.id) as 'ut'

ON c.id = ut.teamid

LIMIT 0 , 30

 

[dinkd]

in your second attempt,

you have aliased

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

as users,

so this line

ON u.id = m.userid

would need to be users.id = m.userid

as the alias 'u' would not be exposed

 

[SilverNodashi]

Ok, I think I'm having an issue understanding how you want the data formatted...... and I'm also not a MySQL person.. i work in MS SQL all day long....

don't think the structure of your query is right though, you can't select columns in the middle of a join unless MySQL is VERY different to MS SQL.

I personally would have started with something like the following then I'd wittle down the data to what I want...

SELECT * from jos_mls_teams mlst LEFT JOIN jos_mls_team_members mlstm ON mlst.id = mlstm.teamid
RIGHT JOIN jos_users us on mlst.id= us.id



Althought looking again... I'd probably have to restructure everything to get the data you want out of MS SQL given the way it handles joins.... :|

Yes, the stucture I have might be incorrect, I'm not 100% sure.

The following statement works as expected though:

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
GROUP BY c.id
LIMIT 0 , 30

i.e. when I remove the RIGHT JOIN clause. so it could be that I've used the wrong JOIN clause (trued outer JOIN, and plain JOIN as well), or it's in the wrong place. but this I don't know

 

[dinkd]

apologies... i think i left out an outer join...

try this..

select c.*, ut.UserCount

FROM 'jos_mls_teams' as 'c'

LEFT OUTER JOIN

(SELECT m.teamid, count(u.id) as 'UserCount'

FROM `jos_mls_teams_members` AS `m`

RIGHT JOIN 'jos_users' as 'u'

ON m.userid = u.id) as 'ut'

ON c.id = ut.teamid

LIMIT 0 , 30

 

[SilverNodashi]

perhaps this will work better... don't have MYSQL so its written in notepad lol

select c.*, ut.UserCount

FROM 'jos_mls_teams' as 'c'

LEFT JOIN

(SELECT m.teamid, count(u.id) as 'UserCount'

FROM `jos_mls_teams_members` AS `m`

RIGHT JOIN 'jos_users' as 'u'

ON m.userid = u.id) as 'ut'

ON c.id = ut.teamid

LIMIT 0 , 30

Yes, that's true, and I did try it with the "users.id" as well, since that's the alias outside the bracket. But it still gives me the error that users.id doesn't exist:

#1054 - Unknown column 'users.id' in 'on clause'

Even substituting all versions of "u." with "users." - i.e. implicating that the "users" alias being in use gives the same error:

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 users.name, users.lastvisitDate FROM `jos_users` AS `u` ) AS `users` 
ON users.id = m.userid
GROUP BY c.id
LIMIT 0 , 30

#1054 - Unknown column 'users.name' in 'field list'

I think at this stage the "u" alias (inside the bracket) is now redundant, and even removing still gives the same error:

#1054 - Unknown column 'users.name' in 'field list'

Taking out the alias clause altogether doesn't work, as stated earlier:

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 jos_users.name, jos_users.lastvisitDate FROM `jos_users`)
ON jos_users.id = m.userid
GROUP BY c.id
LIMIT 0 , 30

error:

#1248 - Every derived table must have its own alias

 

[dinkd]

fixed your last attempt..

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 jos_users.id, jos_users.name, jos_users.lastvisitDate FROM `jos_users`) as 'u'
ON u.id = m.userid
GROUP BY c.id
LIMIT 0 , 30

added in josusers.id to the derived table
aliased it as 'u'

and changed the ON users.id = m.userid to ON u.id = m.userid

ToxicBunny is right though, as far as i know, you need to include all the columns in the query in the group by, so even once you fix the aliasing, you going to run into trouble there.

did you try my rewrite of your query that i posted above?

 

[SilverNodashi]

ok, it's working now, thanx to this post

I shouldn't have queried the alias in the RIGHT SELECT clause. So now the working code is as follows:

SELECT c. * , COUNT( m.id ) AS `members`,u.name,u.lastvisitDate
FROM `jos_mls_teams` AS `c` 
LEFT JOIN `jos_mls_teams_members` AS `m` ON m.teamid = c.id
RIGHT JOIN ( SELECT id, name, lastvisitDate FROM `jos_users` ) AS `u` 
ON u.id = m.userid
GROUP BY c.id
LIMIT 0 , 30

 

[dinkd]

sweet

 

[SilverNodashi]

fixed your last attempt..

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 jos_users.id, jos_users.name, jos_users.lastvisitDate FROM `jos_users`) as 'u'
ON u.id = m.userid
GROUP BY c.id
LIMIT 0 , 30


added in josusers.id to the derived table
aliased it as 'u'

and changed the ON users.id = m.userid to ON u.id = m.userid

Your query is incorrect:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''u' ON u.id = m.userid GROUP BY c.id LIMIT 0 , 30' at line 4

It should be:

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 jos_users.id, jos_users.name, jos_users.lastvisitDate FROM `jos_users`) as `u`
ON u.id = m.userid
GROUP BY c.id
LIMIT 0 , 30

You should use the " ` " and not " ' "

ToxicBunny is right though, as far as i know, you need to include all the columns in the query in the group by, so even once you fix the aliasing, you going to run into trouble there.

I don't know if this rule applies to MySQL though. Using a "select * from ......" clause selects all the columns (wich I did with the jos_mls_teams table), but for the users table I only need id, name, email & lastvisitDate, and MySQL does return it like that

 

[dinkd]

lol those apostrophes got messed up copying and pasting from here to notepad lol

strange how all the DB's have their own complete interpretation of SQL lol *sigh*