Trying to join 2 tables in mysql-Help!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
canadian_angel
Forum Commoner
Posts: 31
Joined: Fri Jun 11, 2010 3:13 pm

Trying to join 2 tables in mysql-Help!

Post by 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 | 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)
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Trying to join 2 tables in mysql-Help!

Post by AbraCadaver »

Maybe because all of the authorid in the joke table are 0, but there is no id of 0 in the author table.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
canadian_angel
Forum Commoner
Posts: 31
Joined: Fri Jun 11, 2010 3:13 pm

Re: Trying to join 2 tables in mysql-Help!

Post by canadian_angel »

I know I am not sure how I got the zeros, in any case I still need to get the authors email addresses and names in the joke table and I dont know how?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Trying to join 2 tables in mysql-Help!

Post by AbraCadaver »

canadian_angel wrote:I know I am not sure how I got the zeros, in any case I still need to get the authors email addresses and names in the joke table and I dont know how?
Well, the authid needs to be equal to some id or you can't. How could you, there is nothing in common?
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply