Troubles with query MySQL

Question

I'm trying to create a query where I select multiple tables and some data of each one but if I make a count it fails. I hope someone can tell me what I'm doing wrong.

This is the one that works fine and brings me all the data:

    SELECT *
            FROM profiles p
                LEFT OUTER JOIN(
                                SELECT u.id
                                    , u.nickname
                                    , u.email
                                    , w.idUserWished
                                    , l.idUserLiked
                                    , b.idBlocked
                                    , m.idMatched
                                    , m.accepted
                                        FROM users u
                                            LEFT OUTER JOIN wish w ON w.idUserWished = u.id
                                            LEFT OUTER JOIN likes l ON l.idUserLiked = u.id
                                            LEFT OUTER JOIN blocked b ON b.idBlocked = u.id
                                            LEFT OUTER JOIN matches m ON m.idMatched = u.id
                                        WHERE NOT EXISTS (
                                                            SELECT idBlock
                                                                FROM blocked b
                                                                WHERE b.idBlock = u.id AND b.idBlocked = 1) 
                                            AND u.id <> 1 
                                            ORDER BY id DESC LIMIT 0,5) AS p2 
                ON p.idUser = p2.id
                    WHERE p.gender = 'Femenino'
                        AND p.age BETWEEN 18 AND 24
                        AND p.alcohol = 'A diario'
                        AND p.smoke = 'A diario'
                        AND p.antro = 'A diario'
                        AND p.edoCivil = 'Casado'
                        AND p.height = 100
                        AND p.weight = 'Delgado'
                        AND p.skin = 'Clara'
                        AND p.location = 'Mexico'
                        AND p.state = 'Coahuila'

But if I include the count, it fails and just bring me only 1 record

    SELECT *
            FROM profiles p
                LEFT OUTER JOIN(
                                SELECT u.id
                                    , u.nickname
                                    , u.email
                                    , w.idUserWished
                                    , l.idUserLiked
                                    , b.idBlocked
                                    , m.idMatched
                                    , m.accepted
                                    , COUNT(DISTINCT msn.UserReceiver) AS 'cantidad'
                                        FROM users u 
                                            LEFT OUTER JOIN wish w ON w.idUserWished = u.id
                                            LEFT OUTER JOIN likes l ON l.idUserLiked = u.id
                                            LEFT OUTER JOIN blocked b ON b.idBlocked = u.id
                                            LEFT OUTER JOIN matches m ON m.idMatched = u.id
                                            LEFT OUTER JOIN messages msn ON msn.userEmitter = 1
                                        WHERE NOT EXISTS (
                                                            SELECT idBlock
                                                                FROM blocked b
                                                                WHERE b.idBlock = u.id AND b.idBlocked = 1) 
                                            AND u.id <> 1 ORDER BY id DESC LIMIT 0,5) AS p2 
                ON p.idUser = p2.id
                    WHERE p.gender = 'Femenino'
                        AND p.age BETWEEN 18 AND 24
                        AND p.alcohol = 'A diario'
                        AND p.smoke = 'A diario'
                        AND p.antro = 'A diario'
                        AND p.edoCivil = 'Casado'
                        AND p.height = 100
                        AND p.weight = 'Delgado'
                        AND p.skin = 'Clara'
                        AND p.location = 'Mexico'
                        AND p.state = 'Coahuila'

An example of the query is the next

Which works

Which does't works

If you can see the difference... when i set the count just bring me 1 nickname and 1 email... but I want that bring me all the data

Answer 1

The problem here is you are using count without group by clause. In another database engine, it causes syntax error but in mysql it still accepts and works. However, let see how mysql work by small test like

    SELECT *, COUNT(*) AS 'cantidad' FROM users

This query return only one record even If table users has many record. Record return is combine of first record in table users and count all users.

So don't use count without group by, It makes your query not clear. Even If mysql still works with this, It is hard to figure out how my sql execute your query

To resolve your problem because you don't provide sample data and expect result so I'm not sure how to resolve your problem exactly. But from your query logic I recommend you change

    , COUNT(DISTINCT msn.UserReceiver) AS 'cantidad'
    ....
    LEFT OUTER JOIN messages msn ON msn.userEmitter = 1

By subquery

    ,(select COUNT(DISTINCT msn.UserReceiver) from messages msn where msn.userEmitter = 1) AS 'cantidad'

So your query will like this

    SELECT *
            FROM profiles p
                LEFT OUTER JOIN(
                                SELECT u.id
                                    , u.nickname
                                    , u.email
                                    , w.idUserWished
                                    , l.idUserLiked
                                    , b.idBlocked
                                    , m.idMatched
                                    , m.accepted
                                    ,(select COUNT(DISTINCT msn.UserReceiver) from messages msn where msn.userEmitter = 1) AS 'cantidad'
                                        FROM users u 
                                            LEFT OUTER JOIN wish w ON w.idUserWished = u.id
                                            LEFT OUTER JOIN likes l ON l.idUserLiked = u.id
                                            LEFT OUTER JOIN blocked b ON b.idBlocked = u.id
                                            LEFT OUTER JOIN matches m ON m.idMatched = u.id
                                        WHERE NOT EXISTS (
                                                            SELECT idBlock
                                                                FROM blocked b
                                                                WHERE b.idBlock = u.id AND b.idBlocked = 1) 
                                            AND u.id <> 1 
                                            ORDER BY id DESC LIMIT 0,5) AS p2 
                ON p.idUser = p2.id
                    WHERE p.gender = 'Femenino'
                        AND p.age BETWEEN 18 AND 24
                        AND p.alcohol = 'A diario'
                        AND p.smoke = 'A diario'
                        AND p.antro = 'A diario'
                        AND p.edoCivil = 'Casado'
                        AND p.height = 100
                        AND p.weight = 'Delgado'
                        AND p.skin = 'Clara'
                        AND p.location = 'Mexico'
                        AND p.state = 'Coahuila'