SELECT joins the same table twice

timsteele · #1 (**permalink**) 12-27-10, 01:02

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.

r937 · #2 (**permalink**) 12-27-10, 06:56

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 )

timsteele · #3 (**permalink**) 12-27-10, 13:51

Quote:

Originally Posted by r937

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?