Douglas S. Davis wrote:
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
This is a "How do I write this query?" question, rather than a Perl DBI
question, so it would be better on the general list. I've copied the general
list so any further discussion can take place there.
Yes, you can do this with a join. You end up joining to the Profiles table
twice, once for the name of the subjec of the quote and once for the source of
the quote. Something like this:
SELECT p1.name AS Subject, c.comment AS Quote, p2.name AS Source
FROM Profiles p1
LEFT JOIN Comments c ON p1.id = c.user_id
LEFT JOIN Profiles p2 ON c.poster_id = p2.id;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
