How to get all results from a query except just one-CodePudding

I have a sqlite database with a few tables the relevant ones for this question being tweet_data and sentiment_t.

tweet_data contains a lot of data about a singular tweet including the user_id from the sender and the tweet id itself. Sentiment_t contains all the tweet_ids and a sentiment associated with the tweet_text.

I want to get all of the sentiments associated with a single user. To do this I wrote the following query:

'''

SELECT sentiment_c
FROM sentiment_t
WHERE sentiment_t.id = (SELECT id
    FROM tweet_data
    WHERE user_id = 1662186764);

'''

I know for sure that there are 15 tweets associated with user_id 1662186764, this query however only returns the first sentiment.

How do I fix this?

CodePudding user response：

With what you've described I'm not sure how you get any result. So let's start.

You have multiple tweets in tweet_data meaning that select id from tweet_data where user_id= 1662186764 will return more than one row. You cannot compare multiple rows with =. You need to use the IN operator.

So correct query should look something like this:

SELECT sentiment_c
FROM sentiment_t
WHERE sentiment_t.id IN (SELECT id
    FROM tweet_data
    WHERE user_id = 1662186764);

(switched operator = with IN)