"subquery returns more than 1 row" error.

Question

I am new to web programming and I'm trying to make a twitter-clone. At this point, I have 3 tables:

users (id, name)

id is the auto-generated id
name of the user

tweets (id, content, user_id)

id is the auto-generated id
content is the text of the tweet
user_id is the id of the user that made the post

followers (id, user_id, following_id)

id is the auto-generated id
user_id is the user who is doing the following
following_id is the user that is being followed

So, being new to sql as well, I am trying to build an SQL statement that would return the tweets that the currently-logged in user and of everyone he follows.

I tried to use this statement, which works sometimes, but other times, I get an error that says "Subquery returns more than 1 row". Here is the statement:

SELECT * FROM tweets 
WHERE user_id IN
((SELECT following_id FROM followers
WHERE user_id = 1),1) ORDER BY date DESC

I put 1 as an example here, which would be the id of the currently logged-in user.

I haven't had any luck with this statement; any help would be greatly appreciated! Thank you.

Since you mentioned you are new to web programming this might be helpfull: http://stackoverflow.com/questions/65512/which-is-faster-best-select-or-select-column1-colum2-column3-etc Its good practice not to use select * incase the table structure changes etc, prob wont affect your project at all but its a good habit to get into using columns rather than select * — Purplegoldfish, Oct 05 '11 at 10:35

MatBailie · Answer 1 · 2011-10-05T09:24:19.030

In one comment you ask is it generally better to use a subquery or a union. Unfortunately, there is no simple answer, just some information.

Some varieties of SQL have problems optimising the IN clause if the lsit is large, and may perform better in any of the following ways...

SELECT * FROM tweets
INNER JOIN followers ON tweets.user_id = followers.following_id
WHERE followers.user_id = 1

UNION ALL

SELECT * FROM tweets
WHERE user_id = 1

Or...

SELECT
  *
FROM
  tweets
INNER JOIN
  (SELECT following_id FROM followers WHERE user_id = 1 UNION SELECT 1) AS followers
    ON tweets.user_id = followers.following_id

Or...

SELECT
  *
FROM
  tweets
WHERE
  EXISTS (SELECT * FROM followers WHERE following_id = tweets.user_id and user_id = 1)
  OR user_id = 1

There are many, many alternatives...

Some varieties of SQL struggle to optimise the OR condition, and end up checking every record in the tweets table instead of utilising an INDEX. This would make the UNION option preferrable, because each half of the query will then benefit from an index on the user_id field.

But you CAN actually refactor this corner case out of your code altogether : Make every user a follower of themselves. This would then mean that getting tweets for followers would naturally include the user themselves. Whether this would make sense in all cases is dependant on your design and other functional requirements.

In short, your best bet is to create some representative data and test the options. But I wouldn't really worry about it for now. As long as you encapsulate this code in one place, you can just pick one that you are most comfortable with. Then, when you have the rest of the system hashed out, and you're much more confident that things won't change, you can go back and optimise if necessary.

100% Agree. SQL gives many and many opportunities, often the right choice for a problem can be a big issue for another scenario. Producing a test case (data amounts, concurrent users) is the only way to choose right a solution! — BigMike, Oct 05 '11 at 09:36

score 4 · Accepted Answer · answered Oct 05 '11 at 08:48

4

SELECT * 
FROM tweets 
WHERE 
  user_id IN (SELECT following_id FROM followers WHERE user_id = 1)
  OR user_id = 1
ORDER BY date DESC

answered Oct 05 '11 at 08:48

Daniel Brink

2,434
4
24
26

thank you, this works as well! Of the two, is it generally better to use a subquery or a union? – kurisukun Oct 05 '11 at 08:58
sub-queries are generally seen as inefficient and not recommended, but seeing that you are still learning sql its best to keep things as simple, short and readable as possible. I didn't want to change your code too much. As Dems says, there are lots of ways to do the same thing in sql, they all have their pros and cons. – Daniel Brink Oct 05 '11 at 14:26

score 2 · Answer 3 · answered Oct 05 '11 at 08:46

try this

SELECT * FROM tweets WHERE user_id = [YourUser]
UNION
SELECT * FROM tweets WHERE user_id in (SELECT following_id FROM followers WHERE user_id ? [YourUser]

shall work even if you've got no followers for your user

There's also a solution with joins, but actually I'm in a hurry. Will try to write the query as soon as I have the time to. Some other will probably answer by then. Sorry.

"subquery returns more than 1 row" error.

3 Answers3

Linked