"subquery returns more than 1 row" error.

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 开发者_如何学Ctweet
• 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.

---

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.

---

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

---

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.