PC Nerd Posted March 31, 2009 Share Posted March 31, 2009 Hi, At the moment I've got two tables: "Sports" and "Groups" - and I've got a PHP script editing another table which is like a "sports by group" (or "events") eg. seperating different groups of competitors (ages, disabilities... whatever the group is)... I need the table to have a static ID for each row/"event" generated.... so I cant use a view because it cant have a primary key. Is there an SQL based alternative, that will allow the same final queryable table/view but that will remove the language side of it (like creating a view with primary keys?) Thanks, PC_Nerd Quote Link to comment Share on other sites More sharing options...
fenway Posted April 2, 2009 Share Posted April 2, 2009 Huh? Quote Link to comment Share on other sites More sharing options...
PC Nerd Posted April 2, 2009 Author Share Posted April 2, 2009 Good point.... two tables, I need to have a third with all of the other two table's worth of data init - linked with a Primary key.... eg: in the new table, for each record in the groups table I would have every record from the sports table... At the moment I can get the data like I want into a view - but those dont allow primary keys ( and understandably)..... At the moment I'm having to "start" the system by using PHP to generate each record into the new table - and then essentially lock access to the other two tables - because changing them could change the new table's data ( which is the "mission critical" thing in the system.... Is there an alternative method to this scripting? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 2, 2009 Share Posted April 2, 2009 I'm sorry, I still don't follow... could you give a concrete example? Quote Link to comment Share on other sites More sharing options...
PC Nerd Posted April 4, 2009 Author Share Posted April 4, 2009 groups -------- group_id int PK group_name varchar upper_limit int lower_limit int sports ------- sport_id int PK name varchar data: groups ------- 1, "Jnr", 12,15 2, "Snr", 16, 19 sports -------- 1,"High Jump" 2, "Long Jump" so that my resulting table would be: events __________ 1, 1( sport_id), 1 (group id) 2, 1, 2 3, 2,1 4,2,2 At the moment I can acheive a similar thing in a view - however the events table cannot have the primary key .... can I get a primary key in there somewhere? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 4, 2009 Share Posted April 4, 2009 What do you mean "resulting table"? Quote Link to comment Share on other sites More sharing options...
PC Nerd Posted April 4, 2009 Author Share Posted April 4, 2009 events is the resulting table.. I'm trying to join the groups and sports together, so that the event table contains every sport for every group - with a uid so that I can reference it with point counts etc.... ? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 4, 2009 Share Posted April 4, 2009 Oh... like a one-time thing? Then try and INSERT INTO... SELECT ... and add an auto_increment column. Quote Link to comment Share on other sites More sharing options...
PC Nerd Posted April 4, 2009 Author Share Posted April 4, 2009 I'm not sure what you mean by the "INSERT INTO... SELECT...". (as in I know the SQL.. but not what its talkign about table wise)..... the events table/view/whatever needs to have a static id to each group/sport id combination ( as I showed in the example events table).... thanks Quote Link to comment Share on other sites More sharing options...
fenway Posted April 4, 2009 Share Posted April 4, 2009 I'm not sure what you mean by the "INSERT INTO... SELECT...". (as in I know the SQL.. but not what its talkign about table wise)..... You can use the resultset from one query as the input for another... the refman has examples. 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.