granger Posted December 30, 2008 Share Posted December 30, 2008 First of all, thanks in advance for any suggestions. I am relatively new to both php and mysql. I have a bunch of tables that use the similar names and exact fields. Below is a snippet of them... table_1: id, team, w, l, rating, rank... table_2: id, team, w, l, rating, rank... table_3: id, team, w, l, rating, rank... the teams and ids are all the same in each table, as this data is taken over time. I want the quickest/best way to output a table with the following way... team, rank(table_1), rating (table_1), rank(table_2), rating (table_2), rank(table_3), rating (table_3) ordered by the rank(table_1) ASC Quote Link to comment Share on other sites More sharing options...
fenway Posted January 2, 2009 Share Posted January 2, 2009 Look at UNION statements. Quote Link to comment Share on other sites More sharing options...
granger Posted January 11, 2009 Author Share Posted January 11, 2009 OK, so that really didn't seem to help since all of the fields within the tables have the same names (either that or I am pulling it out wrong)... Let me give a better example... table_1: id, team, W, L, Rating, Rank 1001, Abbott, 10, 1, 105, 1 1002, Gordon, 9, 2, 100, 2 1003, Strawn, 10, 3, 90, 3 table_2: id, team, W, L, Rating, Rank 1001, Abbott, 10, 1, 103, 1 1002, Gordon, 9, 2, 97, 3 1003, Strawn, 10, 3, 99, 2 table_3: id, team, W, L, Rating, Rank 1001, Abbott, 10, 1, 110, 1 1002, Gordon, 9, 2, 108, 2 1003, Strawn, 10, 3, 96, 3 Ok, so I want to have a query that gives me the following id, Team, W, L, Rating_1, Rank_1, Rating_2, Rank_2, Rating_3, Rank_3 1001, Abbott, 10, 1, 105, 1, 103, 1, 110, 1 1002, Gordon, 9, 2, 100, 2, 97, 3, 108, 2 1003 Strawn, 10, 3, 90, 3, 97, 2, 96, 3 From my understanding, UNIONS and JOIN eliminate fields with the same name, thus I get only one of the rankings or ratings.... do I need to alias the fields within the query? If so, how? Quote Link to comment Share on other sites More sharing options...
Mchl Posted January 11, 2009 Share Posted January 11, 2009 SELECT t1.id AS id1, t2.id AS id2 FROM table_1 AS t1 INNER JOIN table_2 AS t2 USING(id) You should be able to continue from that Quote Link to comment Share on other sites More sharing options...
fenway Posted January 11, 2009 Share Posted January 11, 2009 Sorry, I thought you wanted to show the max from each one. 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.