Return all rows from sub-query SQL?

Question

Now I wrote query SQL for getting rows limited by first query:

SELECT * FROM commenttoarticle a 
WHERE a.idCommentToArticle = (SELECT CommentToArticlePID FROM commenttoarticle b) 
ORDER BY a.idCommentToArticle DESC LIMIT 3

When I try to execute this query, I get:

#1242 - Subquery returns more than 1 row

How to resolve this issue? So, I need get all rows from sub-query.

If I want return one row - I need use GROUP BY, but it is not solution

Modified query:

    SELECT a.idCommentToArticle FROM 
commenttoarticle a WHERE a.CommentToArticlePID IN 
(SELECT idCommentToArticle FROM commenttoarticle b) ORDER BY a.idCommentToArticle DESC LIMIT 3

Dump table commenttoarticle:

CREATE TABLE IF NOT EXISTS `commenttoarticle` (
  `idCommentToArticle` int(11) NOT NULL AUTO_INCREMENT,
  `CommentToArticleTime` int(11) NOT NULL,
  `CommentToArticleIdArticle` int(11) NOT NULL,
  `CommentToArticleComment` text NOT NULL,
  `CommentToArticleIdUser` int(11) NOT NULL,
  `CommentToArticlePID` int(11) NOT NULL,
  PRIMARY KEY (`idCommentToArticle`),
  UNIQUE KEY `idCommentToArticle_UNIQUE` (`idCommentToArticle`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=59 ;

--
-- Дамп данных таблицы `commenttoarticle`
--

INSERT INTO `commenttoarticle` (`idCommentToArticle`, `CommentToArticleTime`, `CommentToArticleIdArticle`, `CommentToArticleComment`, `CommentToArticleIdUser`, `CommentToArticlePID`) VALUES
(29, 0, 11, 'продажам?\nИнтересует не мега-звезда, а именно предметный, руками умеющий продавать сам и помогающий выстраивать это бизнесам.', 459, 0),
(30, 0, 11, '2', 459, 0),
(31, 0, 11, '3', 459, 0),
(36, 0, 11, '3.1', 459, 31),
(37, 1413822798, 11, 'also facing that prob. on the plteform of win 7', 459, 29),
(38, 0, 11, ' here i dont have internet connection.. @Samint Sinha thanks ill check it out maybe tomorrow.', 459, 29),
(39, 0, 11, ' Select max id and you will have dhe last row returned', 459, 29),
(32, 0, 11, '4', 459, 0),
(44, 1414354324, 11, 'How to do', 456, 29),
(45, 1414354469, 11, 'sfsfsf', 456, 29),
(46, 1414354708, 11, 'dddd', 456, 29),
(47, 1414357761, 11, 'sfsfs', 456, 0),
(57, 1414370833, 39, 'kkkppppppp', 456, 0),
(49, 1414358233, 11, 'VSF\nSFSF', 456, 0),
(50, 1414359589, 11, 'How to do', 456, 0),
(51, 1414359660, 11, 'sfsfsdf', 456, 0),
(52, 1414361057, 11, 'SDFSF', 456, 0),
(53, 1414364023, 11, 'dsfdsjfsifmsi', 456, 0),
(54, 1414364031, 11, 'sdfdskjfnskf', 456, 52),
(55, 1414364034, 11, 'sdfdskjfnskf', 456, 52),
(56, 1414364044, 11, 'fndsdfnsofosfi', 456, 52),
(58, 1414370841, 39, 'dfgdfgdgdgdgdgdfgdgdfg', 456, 0);

Result what I need:

Here is example at sqlfiddle: sqlfiddle.com/#!2/dbd82a/1 I need get last 3 rows with a unlimited COMMENTTOARTICLEPID for each first query, if exists. In example, I need get rows with IDCOMMENTTOARTICLE: 58, 57, 56, 52

possible duplicate of [#1242 - Subquery returns more than 1 row - mysql](http://stackoverflow.com/questions/12597620/1242-subquery-returns-more-than-1-row-mysql) — bummi, Oct 27 '14 at 10:32
try (SELECT CommentToArticlePID FROM commenttoarticle b limit 1) OR add some where clause in sub query so that it should return 1 record OR use WHERE a.idCommentToArticle in(SELECT CommentToArticlePID FROM commenttoarticle b) — Deep, Oct 27 '14 at 10:32
Because latest rows is `58, 57, 56` and row `IDCOMMENTTOARTICLE` = `56` has children `COMMENTTOARTICLEPID = 52` — AllenDegrud, Oct 27 '14 at 12:14

score 2 · Answer 1 · answered Oct 27 '14 at 10:39

2

SELECT a.* 
  FROM commenttoarticle a 
  JOIN commenttoarticle b
    ON b.CommentToArticlePID = a.idCommentToArticle 
 ORDER  
    BY a.idCommentToArticle DESC 
 LIMIT 3

answered Oct 27 '14 at 10:39

Strawberry

33,750
13
40
57

Please edit your question accoridingly, showing the desired output from the dataset provided. – Strawberry Oct 27 '14 at 11:01

score 1 · Answer 2 · answered Oct 27 '14 at 10:32

1

use IN instead of '=' before sub-select.

answered Oct 27 '14 at 10:32

Ispirer SQLWays Migrations

1,028
9
16

score 1 · Answer 3 · answered Oct 27 '14 at 10:32

1

USe in instead of ==

SELECT * FROM commenttoarticle a WHERE a.idCommentToArticle in (SELECT CommentToArticlePID FROM commenttoarticle b) ORDER BY a.idCommentToArticle DESC LIMIT 3

answered Oct 27 '14 at 10:32

SonalPM

1,317
8
17

Yes, it works, but show me only 3 rows from first query – AllenDegrud Oct 27 '14 at 10:34
@AllenDegrud: Change Your subquery accordingly. – SonalPM Oct 27 '14 at 10:37

score 1 · Answer 4 · answered Oct 27 '14 at 10:33

Use IN instead of = because your query returns lot of values:

SELECT * FROM commenttoarticle a 
WHERE a.idCommentToArticle IN (
    SELECT CommentToArticlePID 
    FROM commenttoarticle b) 
ORDER BY a.idCommentToArticle DESC LIMIT 3

score 1 · Answer 5 · edited Oct 27 '14 at 11:01

You can use limit in both sub query or main query or both. If you need only 3 result then add limit at main query

SELECT * FROM commenttoarticle a WHERE a.idCommentToArticle IN (SELECT CommentToArticlePID FROM commenttoarticle b) ORDER BY a.idCommentToArticle DESC LIMIT 3

If you want 3 result at sub query then it will be like this

SELECT * FROM commenttoarticle a WHERE a.idCommentToArticle IN (SELECT CommentToArticlePID FROM commenttoarticle b LIMIT 3) ORDER BY a.idCommentToArticle DESC

If you want both

SELECT * FROM commenttoarticle a WHERE a.idCommentToArticle IN (SELECT CommentToArticlePID FROM commenttoarticle b LIMIT 3) ORDER BY a.idCommentToArticle DESC LIMIT 3

`#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' ` — AllenDegrud, Oct 27 '14 at 10:54

score 0 · Accepted Answer · answered Oct 27 '14 at 10:33

I am thinking that your subquery returns more than 1 row hence replace your "=" with "IN". Like this...

SELECT * FROM commenttoarticle a 
WHERE a.idCommentToArticle IN (SELECT CommentToArticlePID FROM commenttoarticle b) 
ORDER BY a.idCommentToArticle DESC LIMIT 3

Return all rows from sub-query SQL?

6 Answers6

Linked