Query Question

Hi,

If the following isn't appropriate, please feel free to ignore.  The 
program I'm referring to is written in Perl and uses a MySQL 
database, so I thought perhaps it would be appropriate for this list.


I have a webpage that displays a user's profile by selecting the data 
in the "profiles" database that corresponds to the user's ID number 
(in the profiles database that number is stored as the "id_num"). I 
have another table that stores comments that are posted to each 
user's profile page.




What I need to do is:

#1)  Display a user's profile by looking up the data in the profiles database.

#2)  Look in the comments database for any comments that have the 
user's id_num associated with it, and display the comments and the 
comment posters' names as found by querying the profile database on 
the comment posters' ID numbers.

Can this be accomplished with a join.

Basically two tables with data similar to this:


Profiles Table
----------------------------
| id |      name      |
________________
| 1 | Bill Clinton    |
---------------------------
| 2 | George Bush |
---------------------------
| 3 | Gerald Ford   |
---------------------------



Comments Table
--------------------------------------------------
| user_id | poster_id | comment     |
--------------------------------------------------
|     1      |      2       | What a guy. |
--------------------------------------------------
|     2      |      3       | What a jerk. |
--------------------------------------------------
|     1      |      3      | Cigar lover.    |
--------------------------------------------------




When I display the profile for Bill Clinton ("id" #1 in the Profiles 
table), it needs to pull the comments made to Bill's profile (the 
ones in the Comments table with the "user_id" matching Bill's id from 
the Profiles table: "What a guy.", and "Cigar lover") and also look 
up the name of the person who made those comments by taking the 
"poster_id" from the Comments table and matching them with a name 
from the first table (for the two comments on Bill's profile, that 
would be "George Bush" and "Gerald Ford").

Can this be done in one SQL query? If so, what would it look like?

Thanks!



                                         Douglas





Douglas S. Davis
Programmer/Analyst
Haverford College
Administrative Computing
370 Lancaster Ave.
Haverford, PA 19041
610-896-4206 


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]