Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    2

    Unanswered: SELECT joins the same table twice

    Summary: I want to select all the profiles that have had issues in the past and have not yet been approved.

    Here's what I have (this is as simple as it can possibly be)-

    'Profile' Table
    int ID
    int CurrentEntryID

    'Entry' Table
    int ID
    int ProfileID
    int Approved
    int Issues

    Some example data-
    Profile
    ID=1, CurrentEntryID=10
    ID=2, CurrentEntryID=20

    Entry
    ID=9, ProfileID=1, Issues=0
    ID=10, ProfileID=1, Approved=0
    ID=19, ProfileID=2, Issues=1
    ID=20, ProfileID=2, Approved=0

    Here's where the fun begins. I need to SELECT the profiles such that-
    1) Entry.Issues > 0 for at least one Entry where Entry.ProfileID = Profile.ID
    2) Profile.CurrentEntryID.Approved = 0

    In English, I want to select all the profiles that have had issues in the past (Entry.Issues > 0) and have not been approved (by an admin) for current issues (CurrentEntry.Approved = 0).

    I've done LEFT JOINs before, but never using the same table like this and I don't know how to branch it.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT profile.id
      FROM profile
    INNER
      JOIN entry
        ON entry.id = profile.currententryid
       AND entry.approved = 0
     WHERE EXISTS
           ( SELECT 'found one'
               FROM entry
              WHERE profileid = profile.id
                AND issues > 0 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    2
    Quote Originally Posted by r937 View Post
    Code:
    SELECT profile.id
      FROM profile
    INNER
      JOIN entry
        ON entry.id = profile.currententryid
       AND entry.approved = 0
     WHERE EXISTS
           ( SELECT 'found one'
               FROM entry
              WHERE profileid = profile.id
                AND issues > 0 )
    Thanks! That's exactly it.

    Unfortunately, I simplified my question a little too much. There's actually another table linking the two, like this-

    'Profile' Table
    int ID
    int CurrentLinkID

    'Link' Table
    int ID
    int ProfileID

    'Entry' Table
    int LinkID
    int Approved
    int Issues

    Some example data-
    Profile
    ID=1, CurrentLinkID=10
    ID=2, CurrentLinkID=20

    Link
    ID=9, ProfileID=1
    ID=10, ProfileID=1
    ID=19, ProfileID=2
    ID=20, ProfileID=2

    Entry
    LinkID=9, Issues=0
    LinkID=10, Approved=0
    LinkID=19, Issues=1
    LinkID=20, Approved=0

    So to get the same information, we have to join Profile to Link on Link.ID=Profile.CurrentLinkID to check for Entry.Approved, then we have to join them on Link.ProfileID=Profile.ID to check for Issues>0. That's what's throwing me off. Anyone know how to do this?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by timsteele View Post
    Thanks! That's exactly it.
    you're welcome


    Quote Originally Posted by timsteele View Post
    Unfortunately, I simplified my question a little too much.
    oh noes!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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