I'm building an application that allows users to recommend music to each other, and am having trouble building a query that would return a 'stream' of recommendations that involve any of the user's friends. So in other words, the recommendation would be returned if either the sender or recipient is a 'friend' of the current user. This is my database structure:RecommendationsID Sender Recipient [other columns...]-- ------ --------- ------------------r1 u1 u3 ...r2 u3 u2 ...r3 u4 u3 ...
UsersID Email First Name Last Name [other columns...]--- ----- ---------- --------- ------------------u1 ... ... ... ...u2 ... ... ... ...u3 ... ... ... ...u4 ... ... ... ...
RelationshipsID Sender Recipient Status [other columns...]--- ------ --------- -------- ------------------rl1 u1 u2 accepted ...rl2 u3 u1 accepted ...rl3 u1 u4 accepted ...rl4 u3 u2 accepted ...
So for user 'u4' (who is friends with 'u1'), I want to query for a 'stream' of recommendations relevant to u4. This stream would include all recommendations in which either the sender or recipient is u1 (the friend).This is my query so far:SELECT recommendations.*, user1.firstname AS 'senderFirstName', user1.lastname AS 'senderLastName', user2.firstname AS 'recipientFirstName', user2.lastname AS 'recipientLastName' FROM recommendations, usersINNER JOIN users user1 ON user1.id=recommendations.senderINNER JOIN users user2 ON user2.id=recommendations.recipientWHERE users.id=recommendations.sender AND (recommendations.sender IN (SELECT sender FROM relationships WHERE recipient='2' AND status='accepted' UNION SELECT recipient FROM relationships WHERE sender='2' AND status='accepted') OR recommendations.recipient IN (SELECT sender FROM relationships WHERE recipient='2' AND status='accepted' UNION SELECT recipient FROM relationships WHERE sender='2' AND status='accepted'))
Now, without the INNER JOINs, this query works correctly, returning all of the records from the 'recommendation' table in which either the sender or recipient is a friend of user 'u4' (specified in the Relationships table). However, the INNER JOINs are causing an error. I'm trying to use them because I also need to get the sender and recipient users' first and last names (stored in the 'Users' table), where the recommendations table just has foreign keys for the sender/recipient.Problem is, I can't figure out how to get that additional info for each of the two users, and especially how to differentiate between the two foreign keys referencing the same table (as shown in the current query, I want to end up with 4 columns for the first and last name of both the sender and recipient, in addition to all the other recommendation table columns).I'm a SQL novice, so any help is appreciated. Thanks!
Question about multiple foreign keys, same table
Posted - 2011-11-17 : 18:49:17
