Discussion :

[Space Cowboy]

Deux tables, une client et une contact.
Un client peut avoir plusieurs contact. Les contact sont lié au client par le champ contact_client dans la table contact. Ce champ contient l'ID du client.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE `booster_client` (
  `client_id` int(9) NOT NULL auto_increment,
  `client_date_creat` datetime NOT NULL default '0000-00-00 00:00:00',
  `client_creat` varchar(254) collate utf8_bin NOT NULL default '',
  `client_date_modif` datetime NOT NULL default '0000-00-00 00:00:00',
  `client_nom` varchar(254) collate utf8_bin NOT NULL default '',
  `client_groupe` varchar(254) collate utf8_bin NOT NULL default '',
  `client_code` varchar(254) collate utf8_bin NOT NULL default '',
  `client_clt` varchar(254) collate utf8_bin NOT NULL default '',
  `client_ic` varchar(254) collate utf8_bin NOT NULL default '',
  `client_potentiel` varchar(254) collate utf8_bin NOT NULL default '',
  `client_secteur` varchar(254) collate utf8_bin NOT NULL default '',
  `client_addr` longtext collate utf8_bin NOT NULL,
  `client_cp` varchar(254) collate utf8_bin NOT NULL default '',
  `client_ville` varchar(254) collate utf8_bin NOT NULL default '',
  `client_tel` varchar(254) collate utf8_bin NOT NULL default '',
  `client_fax` varchar(254) collate utf8_bin NOT NULL default '',
  `client_dpt` varchar(254) collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`client_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin PACK_KEYS=0 AUTO_INCREMENT=4 ;
 
-- 
-- Contenu de la table `booster_client`
-- 
 
INSERT INTO `booster_client` VALUES (1, '2007-03-13 17:38:18', 0x6d6f69, '2007-03-13 17:38:18', 0x31, 0x32, 0x33, 0x34, 0x35, 0x36, 0x37, 0x38, 0x39, 0x3130, 0x3131, 0x3132, 0x3133);
INSERT INTO `booster_client` VALUES (2, '2007-03-14 17:10:40', 0x6d6f69, '2007-03-14 17:10:40', 0x436c69656e7432, 0x47726f75706532, 0x436f646532, 0x434c5432, 0x494332, 0x506f74656e74696132, 0x5365637465757232, 0x4164647232, 0x435032, 0x56696c6c6532, 0x54656c32, 0x46617832, 0x447432);
INSERT INTO `booster_client` VALUES (3, '2007-03-14 17:11:35', 0x6d6f69, '2007-03-14 17:11:35', 0x436c69656e7433, 0x47726f75706533, 0x436f646533, 0x434c5433, 0x494333, 0x506f74656e74696133, 0x5365637465757233, 0x4164647233, 0x435033, 0x56696c6c6533, 0x54656c33, 0x46617833, 0x447433);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE `booster_contact` (
  `contact_id` int(9) NOT NULL auto_increment,
  `contact_date_creat` datetime NOT NULL default '0000-00-00 00:00:00',
  `contact_creat` varchar(254) collate utf8_bin NOT NULL default '',
  `contact_date_modif` datetime NOT NULL default '0000-00-00 00:00:00',
  `contact_client` varchar(254) collate utf8_bin NOT NULL default '',
  `contact_nom` varchar(254) collate utf8_bin NOT NULL default '',
  `contact_prenom` varchar(254) collate utf8_bin NOT NULL default '',
  `contact_sexe` varchar(254) collate utf8_bin NOT NULL default '',
  `contact_fonction` varchar(254) collate utf8_bin NOT NULL default '',
  `contact_code` varchar(254) collate utf8_bin NOT NULL default '',
  `contact_tel` varchar(254) collate utf8_bin NOT NULL default '',
  `contact_fax` varchar(254) collate utf8_bin NOT NULL default '',
  `contact_mobile` varchar(254) collate utf8_bin NOT NULL default '',
  `contact_mail` varchar(254) collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`contact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ;
 
-- 
-- Contenu de la table `booster_contact`
-- 
 
INSERT INTO `booster_contact` VALUES (1, '2007-03-13 18:01:15', 0x6d6f69, '2007-03-13 18:01:15', 0x31, 0x32, 0x33, 0x34, 0x35, 0x36, 0x37, 0x38, 0x39, 0x3130);
INSERT INTO `booster_contact` VALUES (2, '2007-03-13 18:02:08', 0x6d6f69, '2007-03-13 18:02:08', 0x31, 0x32, 0x33, 0x34, 0x35, 0x36, 0x37, 0x38, 0x39, 0x3130);
INSERT INTO `booster_contact` VALUES (3, '2007-03-13 18:02:14', 0x6d6f69, '2007-03-13 18:02:14', 0x31, 0x32, 0x33, 0x34, 0x35, 0x36, 0x37, 0x38, 0x39, 0x3130);
INSERT INTO `booster_contact` VALUES (4, '2007-03-14 17:22:15', 0x6d6f69, '2007-03-14 17:22:15', 0x32, 0x4e6f6d32, 0x5072c3a96e6f6d32, 0x53657865, 0x5344466e, 0x68, 0x6e, 0x68, 0x6a686a6b, 0x66);
INSERT INTO `booster_contact` VALUES (5, '2007-03-14 17:22:24', 0x6d6f69, '2007-03-14 17:22:24', 0x32, 0x4e6f6d3232, 0x5072c3a96e6f6d3232, 0x53657865, 0x5344466e, 0x68, 0x6e, 0x68, 0x6a686a6b, 0x66);
INSERT INTO `booster_contact` VALUES (6, '2007-03-14 17:22:31', 0x6d6f69, '2007-03-14 17:22:31', 0x33, 0x4e6f6d3333, 0x33, 0x33, 0x66, 0x68, 0x6e, 0x68, 0x6a686a6b, 0x66);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT `booster_client`.`client_id`, `booster_client`.`client_nom`, `booster_client`.`client_groupe`, `booster_client`.`client_secteur`, `booster_contact`.`contact_nom` 
		FROM (`booster_client`, `booster_contact`)
		WHERE `booster_client`.`client_id`=`booster_contact`.`contact_client`
		ORDER BY `booster_client`.`client_nom`

J'aimerais selectionner tout les clients, et le nombre de contact.
Quelque chose comme
client1, 3
client2, 2
client3, 1

Pardonnez par avance mon ignorance ...

[Adjanakis]

Bonjour,

Il faut utiliser GROUP BY un peu comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT client_id, count(*) 
  FROM booster_client INNER JOIN booster_contact ON (client_id=contact_client)
  GROUP BY client_id