Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: join same table twice - too many or wrong results

    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.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    As you need only YOUR relationships, the WHERE clause should look like:

    WHERE uc.UserContactsID = 1234
    AND myCharity.userID = your_ID
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2002
    Posts
    189
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Spudhead
    Oh - unless you mean UserContacts?
    doh!! yeah, i got confused, eh

    Quote Originally Posted by Spudhead
    ... and the PK for the table should really just be formed from the userID and the contactID, but... old habits die hard
    yup, the auto_increment id in a relationship table is useless
    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
  •