Group By show when not in the table

Asked

Viewed 26 times

0

I have the table below in Mysql:

inserir a descrição da imagem aqui

I wanted to search the users that has the color BLUE but does not have the color GREEN, IE, in this table would list me user 3 only, I tried so:

SELECT * FROM usuario_cor WHERE tipo = 'azul' AND tipo != 'verde' GROUP BY id_usuario

I know it’s not right, what way to do it?

Thanks!

mysql group-by

1 answer

1


Hey, buddy!
You can do this in two ways. Example:

    SELECT * 
        FROM usuario_cor t1 WHERE t1.Tipo = 'azul' 
        AND NOT EXISTS (SELECT TOP 1 1 
                            FROM usuario_cor t
                        WHERE t.ID_usuario = t1.id_Usuario 
                            AND t.tipo ='verde')
    /*    OU     */

    SELECT t1.* 
        FROM usuario_cor t1
        LEFT JOIN usuario_cor t2 ON t2.id_usuario = t1.id_usuario and t2.tipo = 'verde'
        WHERE t1.Tipo = 'azul' 
        AND t2.id_usuario IS NULL

I hope I’ve helped.
Hug,

Browser other questions tagged mysql group-by

You are not signed in. Login or sign up in order to post.