join same table twice - too many or wrong results

Spudhead · #1 (**permalink**) 08-27-09, 07:42

The query below isn't returning what I want it to.

I want to get a list of a friend's favourite charities, but with an extra field denoting what my relationship with each charity is.

In terms of defining "my" and "friend": I've got a table that manages relationships between users: UserContacts. It's got one field called userID and another called contactID; both link to the main Users table (it's also got an autoincrement PK field). In this sense, "I" am the userID, and my "friend" is the contactID.

I've got, in a similar fashion, a table that manages relationships between users and charities: UserCharities. It's got one field called userID and another called charityID. It's got another integer field that stores the type of relationship - in this case I'm only interested in types 1 and 3.

So, first effort: for a given UserContact ID, get a list of the friend's charities. The "friend" is denoted by the contactID in the UserContacts table, so it's fairly straightfoward:

Code:

SELECT c.charityID, c.charityName
FROM UserContacts uc
INNER JOIN UserCharities friendCharity ON friendCharity.userID = uc.contactID
INNER JOIN Charities c ON c.charityID= friendCharity.charityID
WHERE uc.UserContactsID = 1234
AND friendCharity.relationshipType IN (1,3)
ORDER BY c.charityName

This works fine.

However, when I try to add the second bit - find out the relationship between me and the charities listed (ie: the UserID of the UserContacts instead of the ContactID), I start getting a lot more rows than I expected. This is how I amended it:

Code:

SELECT c.charityID, c.charityName, myCharity.relationshipType 
FROM UserContacts uc
INNER JOIN UserCharities friendCharity ON friendCharity.userID = uc.contactID
INNER JOIN UserCharities myCharity ON myCharity.userID = uc.userID 
INNER JOIN Charities c ON c.charityID= friendCharity.charityID
WHERE uc.UserContactsID = 1234
AND friendCharity.relationshipType IN (1,3)
ORDER BY c.charityName

If I drop a "GROUP BY c.charityID" into it, it doesn't seem to pull out the right data for my relationship with the charity.

As you can probably see, I'm a bit lost. I don't know how I've managed to get it wrong, or what to do to get it right. Any help would be greatly appreciated.

aflorin27 · #2 (**permalink**) 09-02-09, 05:11

As you need only YOUR relationships, the WHERE clause should look like:

WHERE uc.UserContactsID = 1234
AND myCharity.userID = your_ID

r937 · #3 (**permalink**) 09-02-09, 05:49

i'm not sure i used the correct columns (see below) but the structure of the query you need involves a LEFT OUTER JOIN for the specific charity

Code:

SELECT c.charityID
     , c.charityName
     , myCharity.relationshipType 
  FROM UserContacts uc
INNER 
  JOIN UserCharities friendCharity 
    ON friendCharity.userID = uc.contactID
   AND friendCharity.relationshipType IN ( 1,3 )
INNER 
  JOIN Charities c 
    ON c.charityID = friendCharity.charityID
LEFT OUTER
  JOIN UserCharities myCharity 
    ON myCharity.userID = uc.userID 
   AND myCharity.charityID = c.charityID
 WHERE uc.UserContactsID = 1234
ORDER 
    BY c.charityName

the problem i have is that there appears to be three columns in the UserCharities table -- contactID, userID, and UserContactsID

why there are three, i have no idea, but you can see why a guy might get confused if you don't explain which columns are used for what purpose

Spudhead · #4 (**permalink**) 09-02-09, 10:03

Once again: you da man

I had a suspicion that "OUTER" would have to get involved somewhere, but couldn't for the life of me work out how or where. Your solution makes perfect sense, and returns exactly what it should. Thank you.

The UserCharities thing though - I'm not sure where that's come from? UserCharities just stores the relationship between users and their charities, and as such just has columns for userID, charityID, and relationship type.

Oh - unless you mean UserContacts? That's got userID and contactID, for storing the friendship between two individuals (I'm told that in real life it's more complicated), and its own UserContactID PK field. I know I don't need it, and the PK for the table should really just be formed from the userID and the contactID, but... old habits die hard