Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2002
    Location
    california
    Posts
    31

    Talking Unanswered: query with multiple links to one table

    I've been working in Access for years and now need to move some Access queries to mysql/php...looked and looked but can't figure this out.
    I have a table of people (tblPeople): personID, personFname, personLname

    table with interests: intID, refTo, interestID, refBy, personID

    when creating a page to view one person's interests I want to include their name, but I also need to show the refTo name and the refBy name...all of which can also be pulled from the people table.

    so personID, refTo and refBy are all numbers that reference back to the tblPeople.personID

    I can do this in Access ...but how do I do it in mySQL/php query?

    Thanks for any time and attention this deserves. Maybe I just haven't found the best places to look for answers.
    Debbie

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by debbie5154
    I can do this in Access ...but how do I do it in mySQL/php query?
    exactly the same way

    got the access query handy? copy the sql from SQL View, paste it here, and i'll show you...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I concur with Rudy, it is highly likely that you'll be using the same SQL again (minus some MSAccess particulars). Where your learning will need to begin is with PHP. Unfortunately there is not point 'n click interface for this . Begin with php.net and look up "PHP MySQL" on google and you'll find LOTS of tutorials for various ways of doing what you need to do. Once you have a PHP prototype post it in the PHP section of this board and i'm sure someone will find the time to help you progress.

  4. #4
    Join Date
    Sep 2002
    Location
    california
    Posts
    31

    Smile here is the sql

    SELECT tblReferrals.refID, tblPersons.personID, tblPersons.firstName, tblPersons.lastName, tblPersons_1.firstName, tblPersons_1.lastName, tblPersons_2.firstName, tblPersons_2.lastName
    FROM ((tblPersons INNER JOIN tblReferrals ON tblPersons.personID = tblReferrals.personID) INNER JOIN tblPersons AS tblPersons_1 ON tblReferrals.refByID = tblPersons_1.personID) INNER JOIN tblPersons AS tblPersons_2 ON tblReferrals.refToID = tblPersons_2.personID;

    thanks so much

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Correct me if I'm wrong but shouldn't it be a LEFT OUTER JOIN rather than an INNER JOIN?
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that should actually work in mysql

    but i'd prefer to rewrite it without all the wonky Access parentheses, as well as better table alias names...
    Code:
    SELECT tblReferrals.refID
         , tblPersons.personID
         , tblPersons.firstName
         , tblPersons.lastName
         , refByPerson.firstName as refBy_firstName
         , refByPerson.lastName  as refBy_lastName 
         , refToPerson.firstName as refTo_firstName
         , refToPerson.lastName  as refTo_lastName 
      FROM tblPersons 
    INNER 
      JOIN tblReferrals 
        ON tblReferrals.personID = tblPersons.personID
    INNER 
      JOIN tblPersons AS refByPerson 
        ON refByPerson.personID = tblReferrals.refByID
    INNER 
      JOIN tblPersons AS refToPerson 
        ON refToPerson.personID = tblReferrals.refToID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2002
    Location
    california
    Posts
    31

    I'll give it a try

    yes i probably need a left join....my problem was I was trying to use the tblPersons_1 and mysql didn't seem to like that.

    I'll give this a try and let you know.

    Thanks so much for the quick responses.
    deb

  8. #8
    Join Date
    Sep 2002
    Location
    california
    Posts
    31

    Thumbs up It works perfectly

    THANK YOU THANK YOU THANK YOU.
    IT WORKS PERFECTLY.
    and thanks for the tips on sites.
    Debbie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •