[Mysql] Joins

[vinnux]

Gegegevens :
- IIS 5.1 op Windows XP SP1
- PHP 4.2.3
- MySQL 3.23.52

Een kleine schematische weergave van de tabbellen
Tabellen :

User

code:

CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `loginName` varchar(20) NOT NULL default '',
  `password` varchar(20) NOT NULL default '',
  `active` enum('true','false') NOT NULL default 'true',
  `locked` enum('true','false') NOT NULL default 'false',
  `addDate` timestamp(14) NOT NULL,
  `changeDate` timestamp(14) NOT NULL,
  `lastLoginDate` timestamp(14) NOT NULL,
  `nboLogins` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `loginName` (`loginName`),
  UNIQUE KEY `name` (`name`)
) TYPE=MyISAM;

usergroup

code:

CREATE TABLE `usergroup` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `description` text NOT NULL,
  `active` enum('true','false') NOT NULL default 'true',
  `locked` enum('true','false') NOT NULL default 'false',
  `addDate` timestamp(14) NOT NULL,
  `changeDate` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) TYPE=MyISAM;

user_usergroup

code:

CREATE TABLE `user_usergroup` (
  `user_id` int(11) NOT NULL default '0',
  `usergroup_id` int(11) NOT NULL default '0',
  `active` enum('true','false') NOT NULL default 'true',
  `locked` enum('true','false') NOT NULL default 'false',
  `addDate` timestamp(14) NOT NULL,
  `changeDate` timestamp(14) NOT NULL,
  UNIQUE KEY `user_id` (`user_id`,`usergroup_id`)
) TYPE=MyISAM;

Wat ik wil
- Het aantal users dat in een bepaalde usergroup zit.
- Ook usergroups weergeven waar geen users inzitten

Dit moet eigenlijk allemaal in 1 query, omdat ik een overzicht creer van usergroups dat sorteerbaar is op alle mogelijk velden van de onderstaande query en waarvan er ook maar max 50 per pagina gedisplayed worden.

Met de volgende query heb je alle usergroups waar users inzitten, maar niet degene waar geen users inzitten.

code:

SELECT
ug.`id`     AS `id`,
ug.`name`  AS `name`,
ug.`active`  AS `active`,
ug.`locked` AS `locked`,
UNIX_TIMESTAMP(ug.`changeDate`) AS `changeDate`,
UNIX_TIMESTAMP(ug.`addDate`)   AS `addDate`,
COUNT(u_ug.`user_id`)  AS `nboUsers`
FROM
    `usergroup` ug, 
    `user_usergroup`  u_ug
WHERE
    ug.`id` = u_ug.`user_id`
GROUP BY
    ug.`id` 
ORDER BY
  `name` ASC 
LIMIT 0,50

Randvoorwaarden
- Overstappen naar Mysql 4 is geen optie. UNION command.
- Eerst alle usergroeps selecteren en dan per usergroup een nieuwe query voor het ophalen van het aantal users in deze group is eigenlijk geen optie.

Graag zou ik willen weten of het mogelijk om in een query een join en een outerjoin te hebben. Anders zou ik het op prijs stellen als iemand mij een concept zou geven om het probleem op te lossen

[bartvb]

eeeh, gewoon:
FROM usergroup ug LEFT JOIN user_usergroup u_ug ON (ug.id = u_ug.user_id)
Op die manier krijg je al je usergroups, alle u_ug.* fields krijgen de waarde 'NULL' als er geen corresponderende rows zijn.

(Net wakker, lig zelfs nog in bed, kan dus zijn dat bovenstaande verhaal voor geen meter klopt)

[vinnux]

Na heel wat gepuzzel leek dit de goede te zijn !! Klopt dat?

code:

SELECT 
    ug.`id`         AS `id` , 
    ug.`name`   AS `name` , 
    ug.`active` AS `active` , 
    ug.`locked` AS `locked` , 
    UNIX_TIMESTAMP( ug.`changeDate` ) AS `changeDate` , 
    UNIX_TIMESTAMP( ug.`addDate` ) AS `addDate` , 
    COUNT( u_ug.usergroup_id ) AS `nboUsers` 
FROM 
    `usergroup` ug
LEFT JOIN 
    user_usergroup u_ug ON ( ug.id = u_ug.usergroup_id ) 
GROUP BY ug.`id`

[cameodski]

Ziet er goed uit, maar je zult het toch echt zelf moeten gaan testen. Dat gaan wij niet voor je doen tenminste.