i have 2 tables :
med
CREATE TABLE med (id_med INT AUTO_INCREMENT PRIMARY KEY, name_m VARCHAR(50), suame_m VARCHAR(50), spec VARCHAR(50), UNIQUE (suame_m,name_m));viz
CREATE TABLE viz (id_m INT NOT NULL, id_p INT NOT NULL, id_c INT NOT NULL, dt DATETIME)
i need to select the first 3 values for the fullname and count(id_p) for each spec. the following query gives me the first value but i just cant see what to do next.
(SELECT distinct(concat(name_m,' ',suame_m)) AS fullname, COUNT(id_p) as no, (select distinct(spec)) FROM med m JOIN viz v ON m.id_med = v.id_m WHERE year(dt)=2005 GROUP BY spec ORDER BY spec;
any answer will be highly appreciated. thank you.
