Description:
#-----------------------------------------------------------
# With the next structure:
CREATE TABLE metges (
num_met int(11) NOT NULL default '0',
nom_met char(30) default NULL,
especialitzat char(30) default NULL,
adreca char(30) default NULL,
tel int(11) default NULL,
sou int(11) default NULL,
PRIMARY KEY (num_met)
) TYPE=MyISAM;
CREATE TABLE visites (
num_met int(11) NOT NULL default '0',
num_mal int(11) NOT NULL default '0',
data_visita date NOT NULL default '0000-00-00',
import int(11) default NULL,
PRIMARY KEY (num_met,num_mal,data_visita)
) TYPE=MyISAM;
# And next data:
INSERT INTO metges VALUES (1214, 'DOLORS P.', 'RONYO', 'PARIS', 2240257, 150000);
INSERT INTO metges VALUES (12528, 'JORDI G.', 'ESTOMAC', 'ARAGO', 4252746, 150000);
INSERT INTO metges VALUES (7215, 'MIQUEL L.', 'TRAUMATOLOGIA', 'BALMES', 3276072, 100000);
INSERT INTO metges VALUES (15326, 'CARME M.', 'ENDOCRINOLOGIA', 'AMIGO', 4250667, 100000);
INSERT INTO metges VALUES (8517, 'ANNA R.', 'COR I PULMO', 'MUNTANER', 2093085, 250000);
INSERT INTO visites VALUES (1214, 1, '1995-01-15', 5000);
INSERT INTO visites VALUES (1214, 1, '1995-05-05', 2000);
INSERT INTO visites VALUES (1214, 3, '1995-01-22', 7000);
INSERT INTO visites VALUES (12528, 2, '1995-02-04', 3000);
INSERT INTO visites VALUES (12528, 4, '1995-07-23', 5000);
INSERT INTO visites VALUES (12528, 5, '1995-03-07', 5000);
INSERT INTO visites VALUES (12528, 5, '1995-04-20', 3000);
INSERT INTO visites VALUES (12528, 5, '1995-06-30', 5000);
INSERT INTO visites VALUES (7215, 2, '1995-08-17', 7000);
INSERT INTO visites VALUES (7215, 6, '1995-10-15', 10000);
INSERT INTO visites VALUES (7215, 7, '1995-11-24', 10000);
#------------------------------------------------------------
When I try this QUERY:
SELECT m.nom_met, COUNT(DISTINCT v.import)
FROM metges m, visites v
WHERE m.num_met = v.num_met
GROUP BY nom_met
HAVING COUNT(DISTINCT v.import)>2
The server says:
Unknown column 'v.import' in 'having clause'.
The problem is with COUNT(DISTINCT ...) <-- Without the DISTINCT clause, it works. But I'want the other query, not this...
This works on PostgreSQL, Informix or Oracle, for example.
How to repeat:
Suggested fix:
Fix the COUNT(DISTINCT xxx) on the HAVING clause.
