I have two tables: references (top) and tweets (bottom):
tweet_id class_id
------------------
2 1
5 1
6 2
tweet_id text
------------------
1 foo
2 bar
3 baz
4 foobar
5 boobaz
6 foo bar
7 bar baz
I want to retrieve all info of tweets that are in the references table. The desired output is:
tweet_id class_id text
---------------------------
2 1 foo
5 1 foobaz
6 2 foo bar
I'm now using:
SELECT r.tweet_id, t.text, r.class_id
FROM tweets t
LEFT JOIN `references` r ON (r.tweet_id = t.tweet_id)
ORDER BY r.tweet_id ASC LIMIT 100
But this leaves me empty-handed when trying to fetch the tweet_id. The text, however, is displayed:
while($tweet = db_fetch_object($result)) {
echo "$tweet->tweet_id $tweet->text"
}
In the resultset (checked via phpmyadmin), tweet_id and class_id for each record is NULL. What am I doing wrong?
Nevermind :-) I should have LEFT JOIN'ed vice versa:
SELECT r.tweet_id, r.class_id, t.text
FROM `references` r
LEFT JOIN tweets t ON (r.tweet_id = t.tweet_id)
ORDER BY r.tweet_id ASC LIMIT 100