CODEWITHSUNDEEP
mysql
mizan3008
mizan3008

Reputation: 379

what am I doing wrong this SQL query?

If I remove ORDER BY c.campaign_id ASC from below statement it work fine but now it throw me Sub query return more than 1 row.

SELECT SQL_CALC_FOUND_ROWS c.campaign_id, 
    c.title as campaign_title,
    IFNULL((SELECT meta_value 
            FROM campaign_meta as cm
            WHERE cm.campaign_id = c.campaign_id 
            AND cm.meta_key = 'total_viewed'), 0) AS total_viewed, 
    c.campaign_identifier, 
    c.added_datetime,
    l.label_id, 
    l.title AS label_title, 
    l.color, 
    u.username 
FROM campaigns AS c 
LEFT JOIN users AS u ON u.user_id = c.author
LEFT JOIN campaign_relation AS cr ON c.campaign_id = cr.campaign_id
LEFT JOIN labels AS l ON l.label_id = cr.label_id
ORDER BY c.campaign_id ASC
LIMIT 0, 10

Upvotes: 0

Views: 54

Answers (1)

cpoDesign
cpoDesign

Reputation: 9143

your sub query returns more that one row as result

(SELECT meta_value FROM campaign_meta as cm WHERE cm.campaign_id = c.campaign_id AND
cm.meta_key = 'total_viewed')

Where we need to change your query to use sytax:

SELECT ... LIMIT 1

Update it for MySQL

   (SELECT meta_value FROM campaign_meta as cm WHERE cm.campaign_id = c.campaign_id AND
    cm.meta_key = 'total_viewed' LIMIT 1)

or rewrite the query

out of interest to MS SQL syntax

(SELECT TOP 1 meta_value FROM campaign_meta as cm WHERE cm.campaign_id = c.campaign_id AND
cm.meta_key = 'total_viewed')

Upvotes: 2

Related Questions