CODEWITHSUNDEEP
phpmysqlsql
Sam Street
Sam Street

Reputation: 306

PHP/MySQL Subquery returns more than 1 row

This has probably been asked, but as I am a massive PHP/MySQL noob I really don't know what it is I am looking for. I am executing the following query:

SELECT shortlink_analytics.shortlink AS short, 
COUNT(shortlink_analytics.shortlink) AS shortcount, 
(SELECT link FROM shortlinks WHERE shortlinks.shortlink = shortlink_analytics.shortlink)    AS shLink 
FROM shortlink_analytics JOIN shortlinks ON shortlink_analytics.shortlink =   shortlinks.shortlink 
GROUP BY shortlink_analytics.shortlink 
ORDER BY COUNT(shortlink_analytics.shortlink) DESC LIMIT 10

This used to work, but having revisited it in the last week I noticed that the output, instead of working, showed the error of:

Subquery returns more than 1 row

After playing around with the code I have managed to find that the problem lies (I think!) in the area of (but I'm not quite sure):

GROUP BY shortlink_analytics.shortlink 
ORDER BY COUNT(shortlink_analytics.shortlink) DESC LIMIT 10

Would anyone be able to explain:

Upvotes: 0

Views: 668

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

If you want to get all the links on the relationship, use group_concat():

SELECT shortlink_analytics.shortlink AS short, 
       COUNT(shortlink_analytics.shortlink) AS shortcount, 
       group_concat(link) as shlinks
FROM shortlink_analytics JOIN
     shortlinks ON
     shortlink_analytics.shortlink = shortlinks.shortlink 
GROUP BY shortlink_analytics.shortlink 
ORDER BY COUNT(shortlink_analytics.shortlink)
DESC LIMIT 10;

If this guess is wrong, and you need the subquery, you can still get all the links by doing the group_concat() in the subquery:

SELECT shortlink_analytics.shortlink AS short, 
       COUNT(shortlink_analytics.shortlink) AS shortcount, 
       (SELECT group_concat(link)
        FROM shortlinks
        WHERE shortlinks.shortlink = shortlink_analytics.shortlink
       ) AS shLinks
FROM shortlink_analytics JOIN
     shortlinks
     ON shortlink_analytics.shortlink = shortlinks.shortlink 
GROUP BY shortlink_analytics.shortlink 
ORDER BY COUNT(shortlink_analytics.shortlink) DESC
LIMIT 10

Upvotes: 1

Patrick Moore
Patrick Moore

Reputation: 13354

This part is the subquery:

(SELECT link FROM shortlinks WHERE shortlinks.shortlink = shortlink_analytics.shortlink)

Can you simply LIMIT it to 1 row?

(SELECT link FROM shortlinks WHERE shortlinks.shortlink = shortlink_analytics.shortlink LIMIT 1)

You should try to find out why your data is returning multiple rows, and you may even need to ORDER BY to be sure the correct row is returned.

Upvotes: 3

Related Questions