sciencebear Posted October 16, 2009 Share Posted October 16, 2009 I had originally posted this over in the PHP help board, but it turned into a problem that could be better answered over here. This is the query I'm currently running: SELECT t1.username,t1.type,t1.path,t3.body,t4.id FROM updates t1 LEFT JOIN friends t2 ON t1.username=t2.username LEFT JOIN posts t3 ON t1.username=t3.username LEFT JOIN userpics t4 ON t1.path=t4.path WHERE t2.friendname = '$username' OR t2.username = '$username' ORDER BY t1.id DESC $username is defined. I'm trying to find updates from friends of the current users. But there is a problem. Imagine user1 is friend with user2, user3 and user4. They have made updates like this: user2 update 1 user3 update 2 user4 update 3 user2 update 4 user2 update 5 user3 update 6 Instead of returning that, the query returns something like: user2 update 1 user2 update 4 user2 update 5 user3 update 2 user3 update 6 user4 update 3 user2 update 1 user2 update 4 user2 update 5 user2 update 1 user2 update 4 user2 update 5 user3 update 2 user3 update 6 Any suggestions? Quote Link to comment Share on other sites More sharing options...
kickstart Posted October 16, 2009 Share Posted October 16, 2009 Hi Does your friends table have a single row or 2 rows for each friendship? Ie, if Bill and John are friends would you have:- friends username, friendname Bill, John John, Bill If so something like this would do it:- SELECT t2.username,t2.type,t2.path,t3.body,t4.id FROM friends t1 LEFT JOIN updates t2 ON t1.friendname=t2.username LEFT JOIN posts t3 ON t2.username=t3.username LEFT JOIN userpics t4 ON t2.path=t4.path WHERE t1.username = '$username' All the best Keith Quote Link to comment Share on other sites More sharing options...
sciencebear Posted October 16, 2009 Author Share Posted October 16, 2009 It has a single row for each friendship. It doesn't matter which column the friends are in, if two friends are in the same row they are friends. I just recently found a parameter called DISTINCT. Could this do anything to help me? If so, how would I implement it? Quote Link to comment Share on other sites More sharing options...
kickstart Posted October 16, 2009 Share Posted October 16, 2009 Hi You could maybe use distinct, but you shouldn't need to. It would be better to find the underlying issue rather than paper over it. Try something like this:- SELECT t2.username,t2.type,t2.path,t3.body,t4.id FROM (SELECT username AS friendname FROM friends WHERE friendname = '$username' UNION SELECT friendname AS friendname FROM friends WHERE username = '$username' ) t1 LEFT JOIN updates t2 ON t1.friendname=t2.username LEFT JOIN posts t3 ON t2.username=t3.username LEFT JOIN userpics t4 ON t2.path=t4.path This is doing a subselect to get a list of all the users friends and then joining that result with the other tables. All the best Keith Quote Link to comment Share on other sites More sharing options...
sciencebear Posted October 16, 2009 Author Share Posted October 16, 2009 Uhh... not quite working for me. What I ended up doing was join the date stamp across the tables. It works, now. I don't really trust it though. I'd really like a cleaner solution. Quote Link to comment Share on other sites More sharing options...
kickstart Posted October 16, 2009 Share Posted October 16, 2009 Hi Can you post the table layouts and some sample data to play with? Suspect if you have a massive number of posts and userpics for one update then you will get every combination of them returned. All the best Keith Quote Link to comment Share on other sites More sharing options...
sciencebear Posted October 19, 2009 Author Share Posted October 19, 2009 Well besides my query messing up my HTML, the query didn't work like I thought it was. Here's the basic setup and some entries. friends table id friendname username 11 houston02 superman 19 superman kellybean 17 iceman superman 55 superman keving 65 kdizzle superman 77 superman Kevin 98 Vster superman updates table id username type path date 113 iceman picture 10/19/09 : 13:12:18 112 iceman status 10/19/09 : 13:10:43 100 iceman picture 10/19/09 : 12:37:11 97 iceman status 10/16/09 : 17:47:34 95 iceman status 10/16/09 : 14:06:09 94 iceman status 10/16/09 : 13:07:39 88 Kevin status 10/09/09 : 13:54:59 86 iceman status 10/09/09 : 13:17:27 85 iceman status 10/09/09 : 13:17:26 84 Vster status 10/09/09 : 13:12:03 posts table id username body stamp 101 iceman status1 10/19/09 : 13:10:43 88 iceman status2 10/16/09 : 17:47:34 86 iceman status3 10/16/09 : 14:06:09 85 iceman status4 10/16/09 : 13:07:39 81 Kevin status5 10/09/09 : 13:54:59 80 iceman status6 10/09/09 : 13:17:27 79 iceman status7 10/09/09 : 13:17:26 78 Vster status8 10/09/09 : 13:12:03 userpics table id username date filename path 28 iceman 10/19/09 : 13:12:18 [name] 27 iceman 10/19/09 : 12:37:11 [name2] Please note that urls to the pictures in the updates and userpics table match up, but no other urls do. Quote Link to comment Share on other sites More sharing options...
sciencebear Posted October 19, 2009 Author Share Posted October 19, 2009 The current query I am running is: SELECT t2.username,t2.type,t2.path,t3.body,t4.id FROM (SELECT username AS friendname FROM friends WHERE friendname = '$username' UNION SELECT friendname AS friendname FROM friends WHERE username = '$username') t1 LEFT JOIN updates t2 ON t1.friendname=t2.username LEFT JOIN posts t3 ON t2.date=t3.stamp LEFT JOIN userpics t4 ON t2.date=t4.date ORDER BY t2.id DESC This works but I'd like a better solution. Quote Link to comment Share on other sites More sharing options...
kickstart Posted October 21, 2009 Share Posted October 21, 2009 Hi What is the actual problem with that solution? All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.