Group By show when not in table

Question

Group By show when not in table

Navigation

#1 by (1 votes)

0

I have the following table in MySQL:

I wanted to search for users who have the color BLUE but do not have the GREEN color, ie, this table would list me the user 3 only, I tried this way:

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

I know it's not right, how do I do this?

Thanks!

mysql group-by

asked by anonymous 03.12.2018 / 17:23

1 answer

Function executing before the response of my request IONIC - Grid Responsive

score 1 · Accepted Answer

Hello, my friend! 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 have helped. Hugs,