fwapah Posted March 10, 2009 Share Posted March 10, 2009 i have a review system that is setup but it's all in one table. each reviewer has a unique id and 1-5 packets to review. it looks like this, where each reviewer/packet combo has its own line. not the best situation, i know. idreviewerlastpnumpauthorscore 5smith54wilson2.0 5smith99marks1.0 7jones99marks1.3 what i would like to do is be able to combines all of the records with an identical grant number and list their separate scores in the row, so that it looks something like this: pnumpauthorscore1reviewer1score2reviewer2 54wilson2.0smith[/td][td] 99marks1.0smith1.3marks pleeeeeease tell me one of you geniuses can figure this out! thank you so much! Quote Link to comment Share on other sites More sharing options...
fwapah Posted March 11, 2009 Author Share Posted March 11, 2009 bueller? anyone? Quote Link to comment Share on other sites More sharing options...
aschk Posted March 11, 2009 Share Posted March 11, 2009 It's a self join, i.e. JOIN table1 ON table1. Given that you're identity is the "id" column you'll be joining on that. I'm assuming you know how to do a join so i'll leave you to figure out the exact SQL. However, I think what you're ideally looking for is a cross-tab. Needless to say, the self-join will suffice for now. p.s. the join is a LEFT JOIN Quote Link to comment Share on other sites More sharing options...
fwapah Posted March 12, 2009 Author Share Posted March 12, 2009 thanks so much.. sorry for my delayed response. i can still do a left join even though the pnum and id are both not unique? every pnum/id combo has its own line in the table. i need a join where the pnum is listed with its three different scores. thanks so much for your counsel!!! Quote Link to comment Share on other sites More sharing options...
fenway Posted March 13, 2009 Share Posted March 13, 2009 every pnum/id combo has its own line in the table. i need a join where the pnum is listed with its three different scores. Huh? Quote Link to comment Share on other sites More sharing options...
fwapah Posted March 13, 2009 Author Share Posted March 13, 2009 i know, i know, sorry.. it's hard to explain. here is a quick summary, similar to the one above but arranged a little differently. i have: pnumpauthorscoreidreviewerlast 54wilson2.05smith 99marks1.05smith 99marks1.37jones as you can see, the pnum (packet number) and id (of the reviewer of the packet [pnum]) are not unique in this table. i need to display a table that lists each packet (group by pnum) in a line with the three different scores from the three different IDs matched with that pnum. pnumpauthorscore1reviewer1score2reviewer2 54wilson2.0smith[/td][td] 99marks1.0smith1.3marks thank you for taking a look at my messy table and thank you so much for your help! Quote Link to comment Share on other sites More sharing options...
fwapah Posted March 14, 2009 Author Share Posted March 14, 2009 is this pretty much hopeless then? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 15, 2009 Share Posted March 15, 2009 It's not hopeless... why not "massage" the data in script? Quote Link to comment Share on other sites More sharing options...
fwapah Posted March 16, 2009 Author Share Posted March 16, 2009 I've tried to think about this a million different ways, but can't put my mind around how to accomplish this.. hence my posting here. if you have any tips on how to "massage," please let me know, because I am absolutely stumped all around. thanks. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 16, 2009 Share Posted March 16, 2009 Well, you simply sort your results set with the first ID... and then if the next ID isn't different, add a column, otherwise, add a new row, etc. Quote Link to comment Share on other sites More sharing options...
fwapah Posted March 16, 2009 Author Share Posted March 16, 2009 oy... that's way above what my little brain can do. thanks for the patient advice, though! Quote Link to comment Share on other sites More sharing options...
fenway Posted March 16, 2009 Share Posted March 16, 2009 oy... that's way above what my little brain can do. thanks for the patient advice, though! The PHP Help forum is pefect for stuff like this.. do you want me to move this thread? Quote Link to comment Share on other sites More sharing options...
fwapah Posted March 16, 2009 Author Share Posted March 16, 2009 sure, thanks! 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.