CKsk0ddx

1SELECT
2	l.*
3FROM license AS l
4JOIN (
5	SELECT l.license_id, COUNT(u.user_id) AS amount
6	FROM license AS l
7	JOIN user AS u ON u.license_id = l.license_id
8	GROUP BY l.license_id
9) AS t
10	ON l.license_id = t.license_id
11WHERE t.amount < l.amount;
12
13
14-- Adminer 3.3.3 MySQL dump
15
16SET NAMES utf8;
17SET foreign_key_checks = 0;
18SET time_zone = 'SYSTEM';
19SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
20
21DROP TABLE IF EXISTS `license`;
22CREATE TABLE `license` (
23  `license_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
24  `license_key` varchar(20) CHARACTER SET ascii NOT NULL,
25  `amount` int(10) unsigned NOT NULL,
26  `company` varchar(20) NOT NULL,
27  PRIMARY KEY (`license_id`)
28) ENGINE=InnoDB DEFAULT CHARSET=utf8;
29
30INSERT INTO `license` (`license_id`, `license_key`, `amount`, `company`) VALUES
31(1,	'1111111111111',	2,	'Kans'),
32(2,	'2222222222222',	5,	'ABH');
33
34DROP TABLE IF EXISTS `user`;
35CREATE TABLE `user` (
36  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
37  `license_id` int(10) unsigned NOT NULL,
38  `name` varchar(20) NOT NULL,
39  PRIMARY KEY (`user_id`),
40  KEY `license_id` (`license_id`),
41  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`license_id`) REFERENCES `license` (`license_id`)
42) ENGINE=InnoDB DEFAULT CHARSET=utf8;
43
44INSERT INTO `user` (`user_id`, `license_id`, `name`) VALUES
45(1,	1,	'Dino'),
46(2,	2,	'Jef'),
47(3,	1,	'Hans'),
48(4,	2,	'Jef 2');
49
50-- 2012-03-14 13:19:06