Here is a really simple example of a select procedure I'd like to create, although it's only partly done (it's pretty self-explanatory):
delimiter // CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime, _hashid int, _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1)) begin START TRANSACTION; SELECT `first_name`, `last_name`, `hashID` FROM MYDB.person; SELECT`id`, `id_status`, `criminal_status`, `hashID` FROM MYDB.id_crimes; SELECT `stateID`, `stateID_status`, `dob`, `hashID` FROM MYDB.dob_state; SELECT `create_time`, `update_time`, `hashID` FROM MYDB.timestamps; COMMIT; end// delimiter ;
What I'm looking for is the absolutely most concise way to select a record in its entirety, keeping it as a procedure. I'd really like to avoid joins on the tables, but I don't really see any way around doing a join on the hashID, given the abovementioned restrictions. Is there another way? Pointers? What would that look like in a procedure? I'd like to see someone's concise correction of what I want to do. :) Thanks!