0

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 | fredl@home.ca |
| 2 | john adams | johna@hotmail.com |
| 3 | tim horton | timmy@home.ca |
| 4 | colonel sanders | colonels@home.ca |
+----+-----------------+-------------------+
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)

2
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by tesuji
0

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  timmy@home.ca
What is a cows favor  fredl       fredl@home.ca

-- tesu

Edited by tesuji: n/a

0

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

0

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

Edited by tesuji: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.