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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Query Question

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 Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org