jeudi 23 juin 2016

select count with 2 conditions

hy.

i got 2 tables, med and viz with the following structure:

med

  CREATE TABLE med 
     (id_med INT AUTO_INCREMENT PRIMARY KEY,
     name_m VARCHAR(50),
     surname_m VARCHAR(50),
     spec VARCHAR(50),
     UNIQUE (surname_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 a select query that would give the full name from med where count of id_p<250 and year from dt is 2010.

the following query gives me a bad result :

    select count(id_p)<250 as no_p,
       (select distinct concat(name_m,' ',surname_m) from med where id_med=id_m) as fullname
       from viz where year(dt)=2010;




Aucun commentaire:

Enregistrer un commentaire