I want count column per specific user, using data from 3 tables. TABLE 1 (users) : SAMPLE : TABLE 2 (data) : SAMPLE : TABLE 3 (access): SAMPLE : What i tried so far : Result : I what to link it with user name alse, Desired Result : Answer Seems you need also a join for users and as

I want count column per specific user, using data from 3 tables.

TABLE 1 (users) :

CREATE TABLE `datastore`.`users` ( `uid` INT NOT NULL AUTO_INCREMENT ,  `name` VARCHAR(30) NOT NULL DEFAULT 'john' ,  `class` VARCHAR(20) NOT NULL DEFAULT 'NEW' ,    PRIMARY KEY  (`uid`)) ENGINE = InnoDB;
INSERT INTO `users` (`uid`, `name`, `class`) VALUES (NULL, 'john', 'NEW'), (NULL, 'mark', 'OLD');

SAMPLE :

uid name    class
1   john    NEW
2   mark    OLD

TABLE 2 (data) :

CREATE TABLE `datastore`.`data` ( `id` INT NOT NULL AUTO_INCREMENT ,  `source` VARCHAR(30) NULL DEFAULT NULL ,  `destination` VARCHAR(30) NULL DEFAULT NULL ,    PRIMARY KEY  (`id`)) ENGINE = InnoDB;
INSERT INTO `data` (`id`, `source`, `destination`) VALUES (NULL, 'NETWORK', 'SERVER_1'), (NULL, 'STATION', 'SERVER_2'), (NULL, 'DATASTORE', 'SERVER_1');

SAMPLE :

id  source  destination
1   NETWORK     SERVER_1
2   STATION     SERVER_2
3   DATASTORE   SERVER_1

TABLE 3 (access):

CREATE TABLE `datastore`.`access` ( `id` INT(11) NOT NULL AUTO_INCREMENT ,  `uid` INT(11) NULL DEFAULT NULL ,  `source` VARCHAR(30) NULL DEFAULT NULL ,    PRIMARY KEY  (`id`)) ENGINE = InnoDB;
INSERT INTO `access` (`id`, `uid`, `source`) VALUES (NULL, '1', 'NETWORK'), (NULL, '2', 'STATION'), (NULL, '1', 'STATION'), (NULL, '1', 'STATION');

SAMPLE :

id  uid source
1   1   NETWORK
2   2   STATION
3   1   STATION
4   1   STATION

What i tried so far :

SELECT access.uid, data.destination, COUNT(*) as count FROM data, access WHERE access.source = data.source GROUP BY destination, uid

Result :

uid destination count   
1   SERVER_1    1
1   SERVER_2    2
2   SERVER_2    1

I what to link it with user name alse,

Desired Result :

uid name destination    count   
1   john SERVER_1       1
1   john SERVER_2       2
2   mark SERVER_2       1

Answer

Seems you need also a join for users

 SELECT access.uid
  , users.name
  , data.destination
  , COUNT(*) as count 
FROM data
INNER JOIN  access ON  access.source = data.source 
INNER JOIN users ON users.uid = access.uid
GROUP BY destination, uid, users.name

and as suggestion, you should not use the (old) implicit join syntax based on where .. but the explicit join syntax.

Result :

Advertisement

Answer