The databas estructure:
mysql> describe user; +------------------------+-------------+------+-----+---------+------------- ---+ | Field | Type | Null | Key | Default | Extra | +------------------------+-------------+------+-----+---------+------------- ---+ | userID | int(11) | NO | PRI | NULL | auto_increment | | userName | varchar(20) | YES | | NULL | | | userCodeDrivingLicense | varchar(20) | YES | | NULL | | +------------------------+-------------+------+-----+---------+------------- ---+ 3 rows in set (0.00 sec) mysql> describe client; +--------------------------+-------------+------+-----+---------+----------- -----+ | Field | Type | Null | Key | Default | Extra | +--------------------------+-------------+------+-----+---------+----------- -----+ | clientID | int(11) | NO | PRI | NULL | auto_increment | | clientName | varchar(20) | YES | | NULL | | | clientCodeDrivingLicense | varchar(20) | YES | | NULL | | +--------------------------+-------------+------+-----+---------+----------- -----+ 3 rows in set (0.00 sec) RocÃo Gómez Escribano <mailto:r.sanc...@ingenia-soluciones.com> r.go...@ingenia-soluciones.com Descripción: cid:image002.jpg@01CB8CB6.ADEBA830 PolÃgono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 www.ingenia-soluciones.com De: Rocio Gomez Escribano [mailto:r.go...@ingenia-soluciones.com] Enviado el: martes, 03 de mayo de 2011 8:09 Para: mysql@lists.mysql.com Asunto: independent tables Everyone has his/her own driving license, and I need to know what kind of “person” (client or user) is. mysql> select userID, clientID from client, user where (clientCodeDrivingLicense= 321321321 || userCodeDrivingLicense = 321321321); +--------+-------+ | userID | clientID | +--------+-------+ | 1 | 2 | | 2 | 2 | | 3 | 2 | | 4 | 2 | | 5 | 2 | +--------+-------+ 5 rows in set (0.00 sec) But, what I want is something like that: +--------+-------+ | userID | clientID | +--------+-------+ | Null | 2 | +--------+-------+ I tried something like this: select COUNT(DISTINCT u.userID), userID, clientID from client, user where (clientCodeDrivingLicense = 321321321 || userCodeDrivingLicense = 321321321); +--------------------------+--------+-------+ | COUNT(DISTINCT u.userID) | userID | clientID | +--------------------------+--------+-------+ | 5 | 1 | 2 | +--------------------------+--------+-------+ 1 row in set (0.00 sec) But it wont be efficient enough in the future. I suppose my solution is an Join, but they have no intersection, so, I cant imagine how do it Thank you!! Regards RocÃo Gómez Escribano <mailto:r.sanc...@ingenia-soluciones.com> r.go...@ingenia-soluciones.com Descripción: cid:image002.jpg@01CB8CB6.ADEBA830 PolÃgono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 www.ingenia-soluciones.com
