Trying to join 2 tables in mysql-Help!

[canadian_angel]

I am trying to join two tables and havent been very successful, can anyone please help!
The two tables are joke and author; I need to get the authors name and email address in the joke table! The database is ijdb;

mysql> USE ijdb;
Database changed
mysql> SELECT LEFT(joketext, 20), authorid FROM joke;
+----------------------+----------+
| LEFT(joketext, 20) | authorid |
+----------------------+----------+
| Why did the chicken | 0 |
| What is a cows favor | 0 |
+----------------------+----------+
2 rows in set (0.02 sec)

mysql> SELECT * FROM author;
+----+-----------------+-------------------+
| id | name | email |
+----+-----------------+-------------------+
| 1 | fredl | [e-mail] |
| 2 | john adams | [e-mail] |
| 3 | tim horton | [e-mail] |
| 4 | colonel sanders | [e-mail] |
+----+-----------------+-------------------+
4 rows in set (0.00 sec)

mysql> SELECT LEFT(joketext, 20), name, email
-> FROM joke, author WHERE authorid = author.id;
Empty set (0.00 sec)

[tesuji]

Hi,
table joke shouldn't be a joke, therefore column joke.authorid must have proper values, assuming that first joke is from Tim and second from fredl your joke table would then look like:

+----------------------+----------+
| LEFT(joketext, 20) | authorid |
+----------------------+----------+
| Why did the chicken | 3 |
| What is a cows favor | 1 |
+----------------------+----------+

Now your inner join condition "WHERE authorid = author.id" results: 

LEFT(joketext, 20)    name         email  
-----------------------------------------------            
Why did the chicken   tim horton  [e-mail]
What is a cows favor  fredl       [e-mail]

-- tesu

[canadian_angel]

Yes, but how did you get the values in the authorid column, I am not sure whether I should use update or select?

[tesuji]

I see. To modify already stored data update is the appropriate DML statement.

If you have the chance to drop the table and re-insert all rows where you have added the missing authorid, would really be the best way.

If you want to modify the exisitng rows in table joke, you can do it in such a manner:

update joke set authorid = [B]3 WHERE LEFT(joketext, 20)= 'Why did the chicken'[/B];

You see the crucial problem: It is very important that the correct row be identified exactly (bold-faced part in above update example). If not, wrong authorid would be assigned.

Therefore re-inserting corrected data is best method.

-- tesu