Only in my server returns "Subquery returns more than 1 row"-CodePudding

I have a query and it returns the error below:

Warning: mysqli_query(): (21000/1242): Subquery returns more than 1 row in /usr/home/..........

This is the query:

SELECT SQL_CALC_FOUND_ROWS p.*, 
    (SELECT meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND meta_key = 'localidad') AS 'localidad', 
    (SELECT meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND meta_key = 'direccion') AS 'direccion', 
    (SELECT meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND meta_key = 'c.p.') AS 'cp', 
    (SELECT meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND meta_key = 'codigo') AS 'codigo', 
    (SELECT GROUP_CONCAT(t.name SEPARATOR ';') FROM wp_terms t, wp_term_taxonomy tx, wp_term_relationships tr   
        WHERE t.term_id = tx.term_id AND tx.term_taxonomy_id = tr.term_taxonomy_id AND tr.object_id = p.ID AND tx.taxonomy = 'gama' ORDER BY tx.term_taxonomy_id) AS 'gama', 
    (SELECT t.name FROM wp_terms t, wp_term_taxonomy tx, wp_term_relationships tr 
        WHERE t.term_id = tx.term_id AND tx.term_taxonomy_id = tr.term_taxonomy_id AND tr.object_id = p.ID AND tx.taxonomy = 'pais' ORDER BY tx.term_taxonomy_id) AS 'pais', 
    (SELECT t.term_id FROM wp_terms t, wp_term_taxonomy tx, wp_term_relationships tr 
        WHERE t.term_id = tx.term_id AND tx.term_taxonomy_id = tr.term_taxonomy_id AND tr.object_id = p.ID AND tx.taxonomy = 'pais' ORDER BY tx.term_taxonomy_id) AS 'id_pais', 
    (SELECT t.name FROM wp_terms t, wp_term_taxonomy tx, wp_term_relationships tr 
        WHERE t.term_id = tx.term_id AND tx.term_taxonomy_id = tr.term_taxonomy_id AND tr.object_id = p.ID AND tx.taxonomy = 'provincia' ORDER BY tx.term_taxonomy_id) AS 'provincia', 
    (SELECT meta_value FROM wp_postmeta pm 
        WHERE pm.post_id = p.ID AND meta_key = 'localizacion') AS 'localizacion' 
FROM wp_posts p 
WHERE 1=1 AND p.post_type = 'distribuidor' AND (p.post_status = 'publish' OR p.post_status = 'acf-disabled' OR p.post_status = 'private') 
ORDER BY p.post_title ASC

It works in a pair of servers I tried, but not in mine.

Is there any configuration for server to avoid the error? Or is there another way to create the query?

Thank you

CodePudding user response：

It means your server has different data stored in database: there are multiple rows with same meta_key (if error is related with queries with meta_key, if not - adjust column name)

Add GROUP BY meta_key or LIMIT 1 to your sub-queries. E.g.

SELECT SQL_CALC_FOUND_ROWS p.*, 
    (SELECT meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND meta_key = 'localidad' LIMIT 1) AS 'localidad', 
    ...