CODEWITHSUNDEEP
mysqlsqlsubquery
Alper AKPINAR
Alper AKPINAR

Reputation: 91

mysql subquery returns more than 1 row query error

I have many tables for messaging system. But i can't send query to mysql.

SELECT 
            messages_list.IDUSER,
            messages_list.IDPAGE,
            messages_list.IDUSER,
            messages_list.v_readed,
            messages_list.IDUSER,
            messages_texts.v_text,
            messages_texts.v_time,
            messages_subjects.v_title,
            user_photos.v_photoURL,
            user.v_sex,
            (SELECT IDPHOTO FROM user WHERE messages_list.IDUSER)
        FROM 
            messages_texts,
            user
        INNER JOIN user_photos ON user_photos.ID=user.IDPHOTO AND user_photos.v_active='1' 
        INNER JOIN messages_list ON messages_list.IDUSER=717306 AND messages_list.v_active='1'
        INNER JOIN messages_subjects ON messages_list.IDSUBJECT=messages_subjects.ID
        INNER JOIN messages_users ON messages_users.IDSUBJECT=messages_subjects.ID AND messages_users.IDUSER=user.ID
        WHERE
            messages_users.IDUSER<>717306  
        GROUP BY messages_subjects.ID
        ORDER BY messages_list.ID
    )\n: (Subquery returns more than 1 row)

Where is the problem??

Upvotes: 0

Views: 3840

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

The problem is this subquery:

        (SELECT IDPHOTO FROM user WHERE messages_list.IDUSER)

It is nested in a select statement, so it should return only one value. The where clause consists of just the IDUSER value. This will return "true" whenever this is not 0 (presuming the field is an integer). So, it will basically return all users.

Presumably, you want an/the IDPHOTO for a specific user:

        (SELECT IDPHOTO FROM user WHERE messages_list.IDUSER = user.ID)

Upvotes: 2

John Conde
John Conde

Reputation: 219834

A correlated subquery cannot return more than one row.This query:

SELECT IDPHOTO FROM user WHERE messages_list.IDUSER

returns more than one row which it cannot do. You need to limit it to one row only either by using a LIMIT clause:

SELECT IDPHOTO FROM user WHERE messages_list.IDUSER LIMIT 1

Or by using a specific key:

SELECT IDPHOTO FROM user WHERE messages_list.IDUSER WHERE id=1

Upvotes: 0

Related Questions