Charles- OK, let's say a person has `n' interest id entries in tbl_people_interests. It seems like you're wanting a result set with one row per person and (n + 2) (assuming you're selecting first and last name with the interests). I'm no UEbergeek with respect to SQL, but I don't think there's a static query that can do that. I think you'll need to compensate in the PHP script. If you don't get any better responses or don't want to wait, email me off the list and we'll do some php hacking; now would be a good time.
Thanks, -jeff On Sat, 2003-03-15 at 10:44, Charles Kline wrote: > Jeff, > > Thanks. This is ALMOST doing what I need. > > Suppose that the person could have from 0 - 5 records in > tbl_peoples_interests and I need to have each one of those interests as > a different name so I can use it in my PHP form? > > Thanks, > Charles > > > > On Saturday, March 15, 2003, at 11:30 AM, Jeff Kilpatrick wrote: > > > Charles- > > > > So, you have a table unique with respect to people (a person appears at > > most once), and a table with interests (some number of interests are > > associated a person in the first table. This is a one-to-many > > relationship. You need to join on the person in the first table. > > Something like > > > > SELECT > > fname, lname, interest_id > > FROM > > tbl_people, tbl_peoples_interests > > WHERE > > tbl_people.id = tbl_peoples_interests.person > > > > If you have another interests table, it may be more like > > > > SELECT > > fname, lname, interest > > FROM > > tbl_people, tbl_peoples_interests, tbl_interests > > WHERE > > tbl_people.id = tbl_peoples_interests.person AND > > tbl_people_interests.interests.id = interests.id > > > > -jeff > > > > On Sat, 2003-03-15 at 09:23, Charles Kline wrote: > >> Hi all, > >> > >> My first post to this list. > >> > >> I have a query which I can't quite get to work. I was hoping that > >> someone here could educate me a bit. I have two tables. > >> > >> tbl_people > >> > >> | id | fname | lname | > >> --------------------------------------- > >> | 1 | frank | smith | > >> > >> > >> tbl_peoples_interests > >> > >> > >> | id | interest_id | person | > >> ------------------------------------------ > >> | 1 | 500 | 1 | > >> | 2 | 504 | 1 | > >> > >> So... basically what I am trying to do is find the person and join > >> them > >> with their interests, but I need to get ALL their interests and I need > >> to be able to access the values of each interest separately. Here is > >> what I have, but both > >> > >> > >> SELECT DISTINCT p.fname AS firstname, p.lname AS lastname, > >> int_1.interest_id AS int_a, int_2.interest_id AS int_b FROM > >> tbl_personnel AS p, tbl_peoples_interests AS int_1, > >> tbl_peoples_interests AS int_2 WHERE int_1.person = p.id AND > >> int_2.person = p.id AND p.id = 1 > >> > >> what I get back has 500 for both int_a and int_b > >> > >> Thanks > >> Charles > >> > >> > >> > >> > >> --------------------------------------------------------------------- > >> Before posting, please check: > >> http://www.mysql.com/manual.php (the manual) > >> http://lists.mysql.com/ (the list archive) > >> > >> To request this thread, e-mail <[EMAIL PROTECTED]> > >> To unsubscribe, e-mail > >> <[EMAIL PROTECTED]> > >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > >> > >> > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
