CODEWITHSUNDEEP
mysqlsqljoin
Geoffrey
Geoffrey

Reputation: 27

Combining table values in one output in SQL

I have two tables: one called tweets and one called references. tweets consists out of the rows tweet_id and classified amongst others. references consists out of the rows tweet_id and class_id.

The row tweet_id in the table references only consists out of a fraction of the total tweet_ids in the table tweets.

What I would like to do is combine these tables in such a way that the eventual table shows the rows r.tweet_id, t.classified and r.class_id.

I've come up with this query, but for some reason it shows zero rows of output. In reality however, there are about 900 rows in r.tweet_id which all exist in t.tweet_id.

SELECT 't.tweet_id', 't.classified', 'r.tweet_id', 'r.class_id'
FROM `tweets` t, `references` r
WHERE 'r.tweet_id' = 't.tweet_id'

Could somebody tell me what I am doing wrong and how I should change my script in order to get the desired outcome?

Upvotes: 1

Views: 93

Answers (1)

Vyktor
Vyktor

Reputation: 21007

Mysql uses backticks ` to escape schema object names (columns, tables, databases) and apostrophes ' and quotes " to escape strings so you are comparing string r.tweet_id with string t.tweed_id in your condition (which is supposed to be false), do:

SELECT t.tweet_id, t.classified, r.tweet_id, r.class_id
FROM tweets AS t
INNER JOIN `references` AS r ON r.tweet_id = t.tweet_id

Note that you have to just escape word references because it's reserved word in mysql and you can omit other backticks.

Also if you also want to display rows like 1, 2, NULL, NULL (tweets that weren't classified) you can use LEFT JOIN instead of INNER JOIN;if you allow multiple classifications per one tweet, some GROUP BY (Aggregate) Functions may get handy.

BTW: PostgreSQL uses " for schema object names and ' for strings.

Upvotes: 2

Related Questions